1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import typing as t
  20from collections import deque
  21from copy import deepcopy
  22from enum import auto
  23
  24from sqlglot.errors import ParseError
  25from sqlglot.helper import (
  26    AutoName,
  27    camel_to_snake_case,
  28    ensure_collection,
  29    ensure_list,
  30    seq_get,
  31    split_num_words,
  32    subclasses,
  33)
  34from sqlglot.tokens import Token
  35
  36if t.TYPE_CHECKING:
  37    from sqlglot.dialects.dialect import DialectType
  38
  39E = t.TypeVar("E", bound="Expression")
  40
  41
  42class _Expression(type):
  43    def __new__(cls, clsname, bases, attrs):
  44        klass = super().__new__(cls, clsname, bases, attrs)
  45
  46        # When an Expression class is created, its key is automatically set to be
  47        # the lowercase version of the class' name.
  48        klass.key = clsname.lower()
  49
  50        # This is so that docstrings are not inherited in pdoc
  51        klass.__doc__ = klass.__doc__ or ""
  52
  53        return klass
  54
  55
  56class Expression(metaclass=_Expression):
  57    """
  58    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  59    context, such as its child expressions, their names (arg keys), and whether a given child expression
  60    is optional or not.
  61
  62    Attributes:
  63        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  64            and representing expressions as strings.
  65        arg_types: determines what arguments (child nodes) are supported by an expression. It
  66            maps arg keys to booleans that indicate whether the corresponding args are optional.
  67
  68    Example:
  69        >>> class Foo(Expression):
  70        ...     arg_types = {"this": True, "expression": False}
  71
  72        The above definition informs us that Foo is an Expression that requires an argument called
  73        "this" and may also optionally receive an argument called "expression".
  74
  75    Args:
  76        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  77        parent: a reference to the parent expression (or None, in case of root expressions).
  78        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  79            uses to refer to it.
  80        comments: a list of comments that are associated with a given expression. This is used in
  81            order to preserve comments when transpiling SQL code.
  82        _type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  83            optimizer, in order to enable some transformations that require type information.
  84    """
  85
  86    key = "expression"
  87    arg_types = {"this": True}
  88    __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta", "_hash")
  89
  90    def __init__(self, **args: t.Any):
  91        self.args: t.Dict[str, t.Any] = args
  92        self.parent: t.Optional[Expression] = None
  93        self.arg_key: t.Optional[str] = None
  94        self.comments: t.Optional[t.List[str]] = None
  95        self._type: t.Optional[DataType] = None
  96        self._meta: t.Optional[t.Dict[str, t.Any]] = None
  97        self._hash: t.Optional[int] = None
  98
  99        for arg_key, value in self.args.items():
 100            self._set_parent(arg_key, value)
 101
 102    def __eq__(self, other) -> bool:
 103        return type(self) is type(other) and hash(self) == hash(other)
 104
 105    @property
 106    def hashable_args(self) -> t.Any:
 107        args = (self.args.get(k) for k in self.arg_types)
 108
 109        return tuple(
 110            (tuple(_norm_arg(a) for a in arg) if arg else None)
 111            if type(arg) is list
 112            else (_norm_arg(arg) if arg is not None and arg is not False else None)
 113            for arg in args
 114        )
 115
 116    def __hash__(self) -> int:
 117        if self._hash is not None:
 118            return self._hash
 119
 120        return hash((self.__class__, self.hashable_args))
 121
 122    @property
 123    def this(self):
 124        """
 125        Retrieves the argument with key "this".
 126        """
 127        return self.args.get("this")
 128
 129    @property
 130    def expression(self):
 131        """
 132        Retrieves the argument with key "expression".
 133        """
 134        return self.args.get("expression")
 135
 136    @property
 137    def expressions(self):
 138        """
 139        Retrieves the argument with key "expressions".
 140        """
 141        return self.args.get("expressions") or []
 142
 143    def text(self, key) -> str:
 144        """
 145        Returns a textual representation of the argument corresponding to "key". This can only be used
 146        for args that are strings or leaf Expression instances, such as identifiers and literals.
 147        """
 148        field = self.args.get(key)
 149        if isinstance(field, str):
 150            return field
 151        if isinstance(field, (Identifier, Literal, Var)):
 152            return field.this
 153        if isinstance(field, (Star, Null)):
 154            return field.name
 155        return ""
 156
 157    @property
 158    def is_string(self) -> bool:
 159        """
 160        Checks whether a Literal expression is a string.
 161        """
 162        return isinstance(self, Literal) and self.args["is_string"]
 163
 164    @property
 165    def is_number(self) -> bool:
 166        """
 167        Checks whether a Literal expression is a number.
 168        """
 169        return isinstance(self, Literal) and not self.args["is_string"]
 170
 171    @property
 172    def is_int(self) -> bool:
 173        """
 174        Checks whether a Literal expression is an integer.
 175        """
 176        if self.is_number:
 177            try:
 178                int(self.name)
 179                return True
 180            except ValueError:
 181                pass
 182        return False
 183
 184    @property
 185    def is_star(self) -> bool:
 186        """Checks whether an expression is a star."""
 187        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 188
 189    @property
 190    def alias(self) -> str:
 191        """
 192        Returns the alias of the expression, or an empty string if it's not aliased.
 193        """
 194        if isinstance(self.args.get("alias"), TableAlias):
 195            return self.args["alias"].name
 196        return self.text("alias")
 197
 198    @property
 199    def name(self) -> str:
 200        return self.text("this")
 201
 202    @property
 203    def alias_or_name(self):
 204        return self.alias or self.name
 205
 206    @property
 207    def output_name(self):
 208        """
 209        Name of the output column if this expression is a selection.
 210
 211        If the Expression has no output name, an empty string is returned.
 212
 213        Example:
 214            >>> from sqlglot import parse_one
 215            >>> parse_one("SELECT a").expressions[0].output_name
 216            'a'
 217            >>> parse_one("SELECT b AS c").expressions[0].output_name
 218            'c'
 219            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 220            ''
 221        """
 222        return ""
 223
 224    @property
 225    def type(self) -> t.Optional[DataType]:
 226        return self._type
 227
 228    @type.setter
 229    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 230        if dtype and not isinstance(dtype, DataType):
 231            dtype = DataType.build(dtype)
 232        self._type = dtype  # type: ignore
 233
 234    @property
 235    def meta(self) -> t.Dict[str, t.Any]:
 236        if self._meta is None:
 237            self._meta = {}
 238        return self._meta
 239
 240    def __deepcopy__(self, memo):
 241        copy = self.__class__(**deepcopy(self.args))
 242        if self.comments is not None:
 243            copy.comments = deepcopy(self.comments)
 244
 245        if self._type is not None:
 246            copy._type = self._type.copy()
 247
 248        if self._meta is not None:
 249            copy._meta = deepcopy(self._meta)
 250
 251        return copy
 252
 253    def copy(self):
 254        """
 255        Returns a deep copy of the expression.
 256        """
 257        new = deepcopy(self)
 258        new.parent = self.parent
 259        return new
 260
 261    def append(self, arg_key, value):
 262        """
 263        Appends value to arg_key if it's a list or sets it as a new list.
 264
 265        Args:
 266            arg_key (str): name of the list expression arg
 267            value (Any): value to append to the list
 268        """
 269        if not isinstance(self.args.get(arg_key), list):
 270            self.args[arg_key] = []
 271        self.args[arg_key].append(value)
 272        self._set_parent(arg_key, value)
 273
 274    def set(self, arg_key, value):
 275        """
 276        Sets `arg_key` to `value`.
 277
 278        Args:
 279            arg_key (str): name of the expression arg.
 280            value: value to set the arg to.
 281        """
 282        self.args[arg_key] = value
 283        self._set_parent(arg_key, value)
 284
 285    def _set_parent(self, arg_key, value):
 286        if hasattr(value, "parent"):
 287            value.parent = self
 288            value.arg_key = arg_key
 289        elif type(value) is list:
 290            for v in value:
 291                if hasattr(v, "parent"):
 292                    v.parent = self
 293                    v.arg_key = arg_key
 294
 295    @property
 296    def depth(self):
 297        """
 298        Returns the depth of this tree.
 299        """
 300        if self.parent:
 301            return self.parent.depth + 1
 302        return 0
 303
 304    def iter_expressions(self) -> t.Iterator[t.Tuple[str, Expression]]:
 305        """Yields the key and expression for all arguments, exploding list args."""
 306        for k, vs in self.args.items():
 307            if type(vs) is list:
 308                for v in vs:
 309                    if hasattr(v, "parent"):
 310                        yield k, v
 311            else:
 312                if hasattr(vs, "parent"):
 313                    yield k, vs
 314
 315    def find(self, *expression_types: t.Type[E], bfs=True) -> E | None:
 316        """
 317        Returns the first node in this tree which matches at least one of
 318        the specified types.
 319
 320        Args:
 321            expression_types: the expression type(s) to match.
 322
 323        Returns:
 324            The node which matches the criteria or None if no such node was found.
 325        """
 326        return next(self.find_all(*expression_types, bfs=bfs), None)
 327
 328    def find_all(self, *expression_types: t.Type[E], bfs=True) -> t.Iterator[E]:
 329        """
 330        Returns a generator object which visits all nodes in this tree and only
 331        yields those that match at least one of the specified expression types.
 332
 333        Args:
 334            expression_types: the expression type(s) to match.
 335
 336        Returns:
 337            The generator object.
 338        """
 339        for expression, *_ in self.walk(bfs=bfs):
 340            if isinstance(expression, expression_types):
 341                yield expression
 342
 343    def find_ancestor(self, *expression_types: t.Type[E]) -> E | None:
 344        """
 345        Returns a nearest parent matching expression_types.
 346
 347        Args:
 348            expression_types: the expression type(s) to match.
 349
 350        Returns:
 351            The parent node.
 352        """
 353        ancestor = self.parent
 354        while ancestor and not isinstance(ancestor, expression_types):
 355            ancestor = ancestor.parent
 356        return t.cast(E, ancestor)
 357
 358    @property
 359    def parent_select(self):
 360        """
 361        Returns the parent select statement.
 362        """
 363        return self.find_ancestor(Select)
 364
 365    @property
 366    def same_parent(self):
 367        """Returns if the parent is the same class as itself."""
 368        return type(self.parent) is self.__class__
 369
 370    def root(self) -> Expression:
 371        """
 372        Returns the root expression of this tree.
 373        """
 374        expression = self
 375        while expression.parent:
 376            expression = expression.parent
 377        return expression
 378
 379    def walk(self, bfs=True, prune=None):
 380        """
 381        Returns a generator object which visits all nodes in this tree.
 382
 383        Args:
 384            bfs (bool): if set to True the BFS traversal order will be applied,
 385                otherwise the DFS traversal will be used instead.
 386            prune ((node, parent, arg_key) -> bool): callable that returns True if
 387                the generator should stop traversing this branch of the tree.
 388
 389        Returns:
 390            the generator object.
 391        """
 392        if bfs:
 393            yield from self.bfs(prune=prune)
 394        else:
 395            yield from self.dfs(prune=prune)
 396
 397    def dfs(self, parent=None, key=None, prune=None):
 398        """
 399        Returns a generator object which visits all nodes in this tree in
 400        the DFS (Depth-first) order.
 401
 402        Returns:
 403            The generator object.
 404        """
 405        parent = parent or self.parent
 406        yield self, parent, key
 407        if prune and prune(self, parent, key):
 408            return
 409
 410        for k, v in self.iter_expressions():
 411            yield from v.dfs(self, k, prune)
 412
 413    def bfs(self, prune=None):
 414        """
 415        Returns a generator object which visits all nodes in this tree in
 416        the BFS (Breadth-first) order.
 417
 418        Returns:
 419            The generator object.
 420        """
 421        queue = deque([(self, self.parent, None)])
 422
 423        while queue:
 424            item, parent, key = queue.popleft()
 425
 426            yield item, parent, key
 427            if prune and prune(item, parent, key):
 428                continue
 429
 430            for k, v in item.iter_expressions():
 431                queue.append((v, item, k))
 432
 433    def unnest(self):
 434        """
 435        Returns the first non parenthesis child or self.
 436        """
 437        expression = self
 438        while type(expression) is Paren:
 439            expression = expression.this
 440        return expression
 441
 442    def unalias(self):
 443        """
 444        Returns the inner expression if this is an Alias.
 445        """
 446        if isinstance(self, Alias):
 447            return self.this
 448        return self
 449
 450    def unnest_operands(self):
 451        """
 452        Returns unnested operands as a tuple.
 453        """
 454        return tuple(arg.unnest() for _, arg in self.iter_expressions())
 455
 456    def flatten(self, unnest=True):
 457        """
 458        Returns a generator which yields child nodes who's parents are the same class.
 459
 460        A AND B AND C -> [A, B, C]
 461        """
 462        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and not type(n) is self.__class__):
 463            if not type(node) is self.__class__:
 464                yield node.unnest() if unnest else node
 465
 466    def __str__(self):
 467        return self.sql()
 468
 469    def __repr__(self):
 470        return self._to_s()
 471
 472    def sql(self, dialect: DialectType = None, **opts) -> str:
 473        """
 474        Returns SQL string representation of this tree.
 475
 476        Args:
 477            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 478            opts: other `sqlglot.generator.Generator` options.
 479
 480        Returns:
 481            The SQL string.
 482        """
 483        from sqlglot.dialects import Dialect
 484
 485        return Dialect.get_or_raise(dialect)().generate(self, **opts)
 486
 487    def _to_s(self, hide_missing: bool = True, level: int = 0) -> str:
 488        indent = "" if not level else "\n"
 489        indent += "".join(["  "] * level)
 490        left = f"({self.key.upper()} "
 491
 492        args: t.Dict[str, t.Any] = {
 493            k: ", ".join(
 494                v._to_s(hide_missing=hide_missing, level=level + 1)
 495                if hasattr(v, "_to_s")
 496                else str(v)
 497                for v in ensure_list(vs)
 498                if v is not None
 499            )
 500            for k, vs in self.args.items()
 501        }
 502        args["comments"] = self.comments
 503        args["type"] = self.type
 504        args = {k: v for k, v in args.items() if v or not hide_missing}
 505
 506        right = ", ".join(f"{k}: {v}" for k, v in args.items())
 507        right += ")"
 508
 509        return indent + left + right
 510
 511    def transform(self, fun, *args, copy=True, **kwargs):
 512        """
 513        Recursively visits all tree nodes (excluding already transformed ones)
 514        and applies the given transformation function to each node.
 515
 516        Args:
 517            fun (function): a function which takes a node as an argument and returns a
 518                new transformed node or the same node without modifications. If the function
 519                returns None, then the corresponding node will be removed from the syntax tree.
 520            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 521                modified in place.
 522
 523        Returns:
 524            The transformed tree.
 525        """
 526        node = self.copy() if copy else self
 527        new_node = fun(node, *args, **kwargs)
 528
 529        if new_node is None or not isinstance(new_node, Expression):
 530            return new_node
 531        if new_node is not node:
 532            new_node.parent = node.parent
 533            return new_node
 534
 535        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 536        return new_node
 537
 538    def replace(self, expression):
 539        """
 540        Swap out this expression with a new expression.
 541
 542        For example::
 543
 544            >>> tree = Select().select("x").from_("tbl")
 545            >>> tree.find(Column).replace(Column(this="y"))
 546            (COLUMN this: y)
 547            >>> tree.sql()
 548            'SELECT y FROM tbl'
 549
 550        Args:
 551            expression (Expression|None): new node
 552
 553        Returns:
 554            The new expression or expressions.
 555        """
 556        if not self.parent:
 557            return expression
 558
 559        parent = self.parent
 560        self.parent = None
 561
 562        replace_children(parent, lambda child: expression if child is self else child)
 563        return expression
 564
 565    def pop(self):
 566        """
 567        Remove this expression from its AST.
 568
 569        Returns:
 570            The popped expression.
 571        """
 572        self.replace(None)
 573        return self
 574
 575    def assert_is(self, type_):
 576        """
 577        Assert that this `Expression` is an instance of `type_`.
 578
 579        If it is NOT an instance of `type_`, this raises an assertion error.
 580        Otherwise, this returns this expression.
 581
 582        Examples:
 583            This is useful for type security in chained expressions:
 584
 585            >>> import sqlglot
 586            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 587            'SELECT x, z FROM y'
 588        """
 589        assert isinstance(self, type_)
 590        return self
 591
 592    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 593        """
 594        Checks if this expression is valid (e.g. all mandatory args are set).
 595
 596        Args:
 597            args: a sequence of values that were used to instantiate a Func expression. This is used
 598                to check that the provided arguments don't exceed the function argument limit.
 599
 600        Returns:
 601            A list of error messages for all possible errors that were found.
 602        """
 603        errors: t.List[str] = []
 604
 605        for k in self.args:
 606            if k not in self.arg_types:
 607                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 608        for k, mandatory in self.arg_types.items():
 609            v = self.args.get(k)
 610            if mandatory and (v is None or (isinstance(v, list) and not v)):
 611                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 612
 613        if (
 614            args
 615            and isinstance(self, Func)
 616            and len(args) > len(self.arg_types)
 617            and not self.is_var_len_args
 618        ):
 619            errors.append(
 620                f"The number of provided arguments ({len(args)}) is greater than "
 621                f"the maximum number of supported arguments ({len(self.arg_types)})"
 622            )
 623
 624        return errors
 625
 626    def dump(self):
 627        """
 628        Dump this Expression to a JSON-serializable dict.
 629        """
 630        from sqlglot.serde import dump
 631
 632        return dump(self)
 633
 634    @classmethod
 635    def load(cls, obj):
 636        """
 637        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 638        """
 639        from sqlglot.serde import load
 640
 641        return load(obj)
 642
 643
 644IntoType = t.Union[
 645    str,
 646    t.Type[Expression],
 647    t.Collection[t.Union[str, t.Type[Expression]]],
 648]
 649ExpOrStr = t.Union[str, Expression]
 650
 651
 652class Condition(Expression):
 653    def and_(self, *expressions, dialect=None, **opts):
 654        """
 655        AND this condition with one or multiple expressions.
 656
 657        Example:
 658            >>> condition("x=1").and_("y=1").sql()
 659            'x = 1 AND y = 1'
 660
 661        Args:
 662            *expressions (str | Expression): the SQL code strings to parse.
 663                If an `Expression` instance is passed, it will be used as-is.
 664            dialect (str): the dialect used to parse the input expression.
 665            opts (kwargs): other options to use to parse the input expressions.
 666
 667        Returns:
 668            And: the new condition.
 669        """
 670        return and_(self, *expressions, dialect=dialect, **opts)
 671
 672    def or_(self, *expressions, dialect=None, **opts):
 673        """
 674        OR this condition with one or multiple expressions.
 675
 676        Example:
 677            >>> condition("x=1").or_("y=1").sql()
 678            'x = 1 OR y = 1'
 679
 680        Args:
 681            *expressions (str | Expression): the SQL code strings to parse.
 682                If an `Expression` instance is passed, it will be used as-is.
 683            dialect (str): the dialect used to parse the input expression.
 684            opts (kwargs): other options to use to parse the input expressions.
 685
 686        Returns:
 687            Or: the new condition.
 688        """
 689        return or_(self, *expressions, dialect=dialect, **opts)
 690
 691    def not_(self):
 692        """
 693        Wrap this condition with NOT.
 694
 695        Example:
 696            >>> condition("x=1").not_().sql()
 697            'NOT x = 1'
 698
 699        Returns:
 700            Not: the new condition.
 701        """
 702        return not_(self)
 703
 704
 705class Predicate(Condition):
 706    """Relationships like x = y, x > 1, x >= y."""
 707
 708
 709class DerivedTable(Expression):
 710    @property
 711    def alias_column_names(self):
 712        table_alias = self.args.get("alias")
 713        if not table_alias:
 714            return []
 715        column_list = table_alias.assert_is(TableAlias).args.get("columns") or []
 716        return [c.name for c in column_list]
 717
 718    @property
 719    def selects(self):
 720        alias = self.args.get("alias")
 721
 722        if alias:
 723            return alias.columns
 724        return []
 725
 726    @property
 727    def named_selects(self):
 728        return [select.output_name for select in self.selects]
 729
 730
 731class Unionable(Expression):
 732    def union(self, expression, distinct=True, dialect=None, **opts):
 733        """
 734        Builds a UNION expression.
 735
 736        Example:
 737            >>> import sqlglot
 738            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
 739            'SELECT * FROM foo UNION SELECT * FROM bla'
 740
 741        Args:
 742            expression (str | Expression): the SQL code string.
 743                If an `Expression` instance is passed, it will be used as-is.
 744            distinct (bool): set the DISTINCT flag if and only if this is true.
 745            dialect (str): the dialect used to parse the input expression.
 746            opts (kwargs): other options to use to parse the input expressions.
 747        Returns:
 748            Union: the Union expression.
 749        """
 750        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 751
 752    def intersect(self, expression, distinct=True, dialect=None, **opts):
 753        """
 754        Builds an INTERSECT expression.
 755
 756        Example:
 757            >>> import sqlglot
 758            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
 759            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
 760
 761        Args:
 762            expression (str | Expression): the SQL code string.
 763                If an `Expression` instance is passed, it will be used as-is.
 764            distinct (bool): set the DISTINCT flag if and only if this is true.
 765            dialect (str): the dialect used to parse the input expression.
 766            opts (kwargs): other options to use to parse the input expressions.
 767        Returns:
 768            Intersect: the Intersect expression
 769        """
 770        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 771
 772    def except_(self, expression, distinct=True, dialect=None, **opts):
 773        """
 774        Builds an EXCEPT expression.
 775
 776        Example:
 777            >>> import sqlglot
 778            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
 779            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
 780
 781        Args:
 782            expression (str | Expression): the SQL code string.
 783                If an `Expression` instance is passed, it will be used as-is.
 784            distinct (bool): set the DISTINCT flag if and only if this is true.
 785            dialect (str): the dialect used to parse the input expression.
 786            opts (kwargs): other options to use to parse the input expressions.
 787        Returns:
 788            Except: the Except expression
 789        """
 790        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 791
 792
 793class UDTF(DerivedTable, Unionable):
 794    pass
 795
 796
 797class Cache(Expression):
 798    arg_types = {
 799        "with": False,
 800        "this": True,
 801        "lazy": False,
 802        "options": False,
 803        "expression": False,
 804    }
 805
 806
 807class Uncache(Expression):
 808    arg_types = {"this": True, "exists": False}
 809
 810
 811class Create(Expression):
 812    arg_types = {
 813        "with": False,
 814        "this": True,
 815        "kind": True,
 816        "expression": False,
 817        "exists": False,
 818        "properties": False,
 819        "replace": False,
 820        "unique": False,
 821        "indexes": False,
 822        "no_schema_binding": False,
 823        "begin": False,
 824    }
 825
 826
 827class Describe(Expression):
 828    arg_types = {"this": True, "kind": False}
 829
 830
 831class Pragma(Expression):
 832    pass
 833
 834
 835class Set(Expression):
 836    arg_types = {"expressions": False}
 837
 838
 839class SetItem(Expression):
 840    arg_types = {
 841        "this": False,
 842        "expressions": False,
 843        "kind": False,
 844        "collate": False,  # MySQL SET NAMES statement
 845        "global": False,
 846    }
 847
 848
 849class Show(Expression):
 850    arg_types = {
 851        "this": True,
 852        "target": False,
 853        "offset": False,
 854        "limit": False,
 855        "like": False,
 856        "where": False,
 857        "db": False,
 858        "full": False,
 859        "mutex": False,
 860        "query": False,
 861        "channel": False,
 862        "global": False,
 863        "log": False,
 864        "position": False,
 865        "types": False,
 866    }
 867
 868
 869class UserDefinedFunction(Expression):
 870    arg_types = {"this": True, "expressions": False, "wrapped": False}
 871
 872
 873class CharacterSet(Expression):
 874    arg_types = {"this": True, "default": False}
 875
 876
 877class With(Expression):
 878    arg_types = {"expressions": True, "recursive": False}
 879
 880    @property
 881    def recursive(self) -> bool:
 882        return bool(self.args.get("recursive"))
 883
 884
 885class WithinGroup(Expression):
 886    arg_types = {"this": True, "expression": False}
 887
 888
 889class CTE(DerivedTable):
 890    arg_types = {"this": True, "alias": True}
 891
 892
 893class TableAlias(Expression):
 894    arg_types = {"this": False, "columns": False}
 895
 896    @property
 897    def columns(self):
 898        return self.args.get("columns") or []
 899
 900
 901class BitString(Condition):
 902    pass
 903
 904
 905class HexString(Condition):
 906    pass
 907
 908
 909class ByteString(Condition):
 910    pass
 911
 912
 913class Column(Condition):
 914    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
 915
 916    @property
 917    def table(self) -> str:
 918        return self.text("table")
 919
 920    @property
 921    def db(self) -> str:
 922        return self.text("db")
 923
 924    @property
 925    def catalog(self) -> str:
 926        return self.text("catalog")
 927
 928    @property
 929    def output_name(self) -> str:
 930        return self.name
 931
 932    @property
 933    def parts(self) -> t.List[Identifier]:
 934        """Return the parts of a column in order catalog, db, table, name."""
 935        return [part for part in reversed(list(self.args.values())) if part]
 936
 937    def to_dot(self) -> Dot:
 938        """Converts the column into a dot expression."""
 939        parts = self.parts
 940        parent = self.parent
 941
 942        while parent:
 943            if isinstance(parent, Dot):
 944                parts.append(parent.expression)
 945            parent = parent.parent
 946
 947        return Dot.build(parts)
 948
 949
 950class ColumnPosition(Expression):
 951    arg_types = {"this": False, "position": True}
 952
 953
 954class ColumnDef(Expression):
 955    arg_types = {
 956        "this": True,
 957        "kind": False,
 958        "constraints": False,
 959        "exists": False,
 960        "position": False,
 961    }
 962
 963
 964class AlterColumn(Expression):
 965    arg_types = {
 966        "this": True,
 967        "dtype": False,
 968        "collate": False,
 969        "using": False,
 970        "default": False,
 971        "drop": False,
 972    }
 973
 974
 975class RenameTable(Expression):
 976    pass
 977
 978
 979class SetTag(Expression):
 980    arg_types = {"expressions": True, "unset": False}
 981
 982
 983class Comment(Expression):
 984    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
 985
 986
 987class ColumnConstraint(Expression):
 988    arg_types = {"this": False, "kind": True}
 989
 990
 991class ColumnConstraintKind(Expression):
 992    pass
 993
 994
 995class AutoIncrementColumnConstraint(ColumnConstraintKind):
 996    pass
 997
 998
 999class CaseSpecificColumnConstraint(ColumnConstraintKind):
1000    arg_types = {"not_": True}
1001
1002
1003class CharacterSetColumnConstraint(ColumnConstraintKind):
1004    arg_types = {"this": True}
1005
1006
1007class CheckColumnConstraint(ColumnConstraintKind):
1008    pass
1009
1010
1011class CollateColumnConstraint(ColumnConstraintKind):
1012    pass
1013
1014
1015class CommentColumnConstraint(ColumnConstraintKind):
1016    pass
1017
1018
1019class CompressColumnConstraint(ColumnConstraintKind):
1020    pass
1021
1022
1023class DateFormatColumnConstraint(ColumnConstraintKind):
1024    arg_types = {"this": True}
1025
1026
1027class DefaultColumnConstraint(ColumnConstraintKind):
1028    pass
1029
1030
1031class EncodeColumnConstraint(ColumnConstraintKind):
1032    pass
1033
1034
1035class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1036    # this: True -> ALWAYS, this: False -> BY DEFAULT
1037    arg_types = {
1038        "this": False,
1039        "start": False,
1040        "increment": False,
1041        "minvalue": False,
1042        "maxvalue": False,
1043        "cycle": False,
1044    }
1045
1046
1047class InlineLengthColumnConstraint(ColumnConstraintKind):
1048    pass
1049
1050
1051class NotNullColumnConstraint(ColumnConstraintKind):
1052    arg_types = {"allow_null": False}
1053
1054
1055# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1056class OnUpdateColumnConstraint(ColumnConstraintKind):
1057    pass
1058
1059
1060class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1061    arg_types = {"desc": False}
1062
1063
1064class TitleColumnConstraint(ColumnConstraintKind):
1065    pass
1066
1067
1068class UniqueColumnConstraint(ColumnConstraintKind):
1069    arg_types: t.Dict[str, t.Any] = {}
1070
1071
1072class UppercaseColumnConstraint(ColumnConstraintKind):
1073    arg_types: t.Dict[str, t.Any] = {}
1074
1075
1076class PathColumnConstraint(ColumnConstraintKind):
1077    pass
1078
1079
1080class Constraint(Expression):
1081    arg_types = {"this": True, "expressions": True}
1082
1083
1084class Delete(Expression):
1085    arg_types = {"with": False, "this": False, "using": False, "where": False, "returning": False}
1086
1087    def delete(
1088        self,
1089        table: ExpOrStr,
1090        dialect: DialectType = None,
1091        copy: bool = True,
1092        **opts,
1093    ) -> Delete:
1094        """
1095        Create a DELETE expression or replace the table on an existing DELETE expression.
1096
1097        Example:
1098            >>> delete("tbl").sql()
1099            'DELETE FROM tbl'
1100
1101        Args:
1102            table: the table from which to delete.
1103            dialect: the dialect used to parse the input expression.
1104            copy: if `False`, modify this expression instance in-place.
1105            opts: other options to use to parse the input expressions.
1106
1107        Returns:
1108            Delete: the modified expression.
1109        """
1110        return _apply_builder(
1111            expression=table,
1112            instance=self,
1113            arg="this",
1114            dialect=dialect,
1115            into=Table,
1116            copy=copy,
1117            **opts,
1118        )
1119
1120    def where(
1121        self,
1122        *expressions: ExpOrStr,
1123        append: bool = True,
1124        dialect: DialectType = None,
1125        copy: bool = True,
1126        **opts,
1127    ) -> Delete:
1128        """
1129        Append to or set the WHERE expressions.
1130
1131        Example:
1132            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1133            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1134
1135        Args:
1136            *expressions: the SQL code strings to parse.
1137                If an `Expression` instance is passed, it will be used as-is.
1138                Multiple expressions are combined with an AND operator.
1139            append: if `True`, AND the new expressions to any existing expression.
1140                Otherwise, this resets the expression.
1141            dialect: the dialect used to parse the input expressions.
1142            copy: if `False`, modify this expression instance in-place.
1143            opts: other options to use to parse the input expressions.
1144
1145        Returns:
1146            Delete: the modified expression.
1147        """
1148        return _apply_conjunction_builder(
1149            *expressions,
1150            instance=self,
1151            arg="where",
1152            append=append,
1153            into=Where,
1154            dialect=dialect,
1155            copy=copy,
1156            **opts,
1157        )
1158
1159    def returning(
1160        self,
1161        expression: ExpOrStr,
1162        dialect: DialectType = None,
1163        copy: bool = True,
1164        **opts,
1165    ) -> Delete:
1166        """
1167        Set the RETURNING expression. Not supported by all dialects.
1168
1169        Example:
1170            >>> delete("tbl").returning("*", dialect="postgres").sql()
1171            'DELETE FROM tbl RETURNING *'
1172
1173        Args:
1174            expression: the SQL code strings to parse.
1175                If an `Expression` instance is passed, it will be used as-is.
1176            dialect: the dialect used to parse the input expressions.
1177            copy: if `False`, modify this expression instance in-place.
1178            opts: other options to use to parse the input expressions.
1179
1180        Returns:
1181            Delete: the modified expression.
1182        """
1183        return _apply_builder(
1184            expression=expression,
1185            instance=self,
1186            arg="returning",
1187            prefix="RETURNING",
1188            dialect=dialect,
1189            copy=copy,
1190            into=Returning,
1191            **opts,
1192        )
1193
1194
1195class Drop(Expression):
1196    arg_types = {
1197        "this": False,
1198        "kind": False,
1199        "exists": False,
1200        "temporary": False,
1201        "materialized": False,
1202        "cascade": False,
1203        "constraints": False,
1204        "purge": False,
1205    }
1206
1207
1208class Filter(Expression):
1209    arg_types = {"this": True, "expression": True}
1210
1211
1212class Check(Expression):
1213    pass
1214
1215
1216class Directory(Expression):
1217    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1218    arg_types = {"this": True, "local": False, "row_format": False}
1219
1220
1221class ForeignKey(Expression):
1222    arg_types = {
1223        "expressions": True,
1224        "reference": False,
1225        "delete": False,
1226        "update": False,
1227    }
1228
1229
1230class PrimaryKey(Expression):
1231    arg_types = {"expressions": True, "options": False}
1232
1233
1234class Unique(Expression):
1235    arg_types = {"expressions": True}
1236
1237
1238# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1239# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1240class Into(Expression):
1241    arg_types = {"this": True, "temporary": False, "unlogged": False}
1242
1243
1244class From(Expression):
1245    arg_types = {"expressions": True}
1246
1247
1248class Having(Expression):
1249    pass
1250
1251
1252class Hint(Expression):
1253    arg_types = {"expressions": True}
1254
1255
1256class JoinHint(Expression):
1257    arg_types = {"this": True, "expressions": True}
1258
1259
1260class Identifier(Expression):
1261    arg_types = {"this": True, "quoted": False}
1262
1263    @property
1264    def quoted(self):
1265        return bool(self.args.get("quoted"))
1266
1267    @property
1268    def hashable_args(self) -> t.Any:
1269        if self.quoted and any(char.isupper() for char in self.this):
1270            return (self.this, self.quoted)
1271        return self.this.lower()
1272
1273    @property
1274    def output_name(self):
1275        return self.name
1276
1277
1278class Index(Expression):
1279    arg_types = {
1280        "this": False,
1281        "table": False,
1282        "where": False,
1283        "columns": False,
1284        "unique": False,
1285        "primary": False,
1286        "amp": False,  # teradata
1287    }
1288
1289
1290class Insert(Expression):
1291    arg_types = {
1292        "with": False,
1293        "this": True,
1294        "expression": False,
1295        "returning": False,
1296        "overwrite": False,
1297        "exists": False,
1298        "partition": False,
1299        "alternative": False,
1300    }
1301
1302
1303class Returning(Expression):
1304    arg_types = {"expressions": True}
1305
1306
1307# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1308class Introducer(Expression):
1309    arg_types = {"this": True, "expression": True}
1310
1311
1312# national char, like n'utf8'
1313class National(Expression):
1314    pass
1315
1316
1317class LoadData(Expression):
1318    arg_types = {
1319        "this": True,
1320        "local": False,
1321        "overwrite": False,
1322        "inpath": True,
1323        "partition": False,
1324        "input_format": False,
1325        "serde": False,
1326    }
1327
1328
1329class Partition(Expression):
1330    arg_types = {"expressions": True}
1331
1332
1333class Fetch(Expression):
1334    arg_types = {
1335        "direction": False,
1336        "count": False,
1337        "percent": False,
1338        "with_ties": False,
1339    }
1340
1341
1342class Group(Expression):
1343    arg_types = {
1344        "expressions": False,
1345        "grouping_sets": False,
1346        "cube": False,
1347        "rollup": False,
1348    }
1349
1350
1351class Lambda(Expression):
1352    arg_types = {"this": True, "expressions": True}
1353
1354
1355class Limit(Expression):
1356    arg_types = {"this": False, "expression": True}
1357
1358
1359class Literal(Condition):
1360    arg_types = {"this": True, "is_string": True}
1361
1362    @property
1363    def hashable_args(self) -> t.Any:
1364        return (self.this, self.args.get("is_string"))
1365
1366    @classmethod
1367    def number(cls, number) -> Literal:
1368        return cls(this=str(number), is_string=False)
1369
1370    @classmethod
1371    def string(cls, string) -> Literal:
1372        return cls(this=str(string), is_string=True)
1373
1374    @property
1375    def output_name(self):
1376        return self.name
1377
1378
1379class Join(Expression):
1380    arg_types = {
1381        "this": True,
1382        "on": False,
1383        "side": False,
1384        "kind": False,
1385        "using": False,
1386        "natural": False,
1387        "hint": False,
1388    }
1389
1390    @property
1391    def kind(self):
1392        return self.text("kind").upper()
1393
1394    @property
1395    def side(self):
1396        return self.text("side").upper()
1397
1398    @property
1399    def hint(self):
1400        return self.text("hint").upper()
1401
1402    @property
1403    def alias_or_name(self):
1404        return self.this.alias_or_name
1405
1406    def on(self, *expressions, append=True, dialect=None, copy=True, **opts):
1407        """
1408        Append to or set the ON expressions.
1409
1410        Example:
1411            >>> import sqlglot
1412            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
1413            'JOIN x ON y = 1'
1414
1415        Args:
1416            *expressions (str | Expression): the SQL code strings to parse.
1417                If an `Expression` instance is passed, it will be used as-is.
1418                Multiple expressions are combined with an AND operator.
1419            append (bool): if `True`, AND the new expressions to any existing expression.
1420                Otherwise, this resets the expression.
1421            dialect (str): the dialect used to parse the input expressions.
1422            copy (bool): if `False`, modify this expression instance in-place.
1423            opts (kwargs): other options to use to parse the input expressions.
1424
1425        Returns:
1426            Join: the modified join expression.
1427        """
1428        join = _apply_conjunction_builder(
1429            *expressions,
1430            instance=self,
1431            arg="on",
1432            append=append,
1433            dialect=dialect,
1434            copy=copy,
1435            **opts,
1436        )
1437
1438        if join.kind == "CROSS":
1439            join.set("kind", None)
1440
1441        return join
1442
1443    def using(self, *expressions, append=True, dialect=None, copy=True, **opts):
1444        """
1445        Append to or set the USING expressions.
1446
1447        Example:
1448            >>> import sqlglot
1449            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
1450            'JOIN x USING (foo, bla)'
1451
1452        Args:
1453            *expressions (str | Expression): the SQL code strings to parse.
1454                If an `Expression` instance is passed, it will be used as-is.
1455            append (bool): if `True`, concatenate the new expressions to the existing "using" list.
1456                Otherwise, this resets the expression.
1457            dialect (str): the dialect used to parse the input expressions.
1458            copy (bool): if `False`, modify this expression instance in-place.
1459            opts (kwargs): other options to use to parse the input expressions.
1460
1461        Returns:
1462            Join: the modified join expression.
1463        """
1464        join = _apply_list_builder(
1465            *expressions,
1466            instance=self,
1467            arg="using",
1468            append=append,
1469            dialect=dialect,
1470            copy=copy,
1471            **opts,
1472        )
1473
1474        if join.kind == "CROSS":
1475            join.set("kind", None)
1476
1477        return join
1478
1479
1480class Lateral(UDTF):
1481    arg_types = {"this": True, "view": False, "outer": False, "alias": False}
1482
1483
1484class MatchRecognize(Expression):
1485    arg_types = {
1486        "partition_by": False,
1487        "order": False,
1488        "measures": False,
1489        "rows": False,
1490        "after": False,
1491        "pattern": False,
1492        "define": False,
1493        "alias": False,
1494    }
1495
1496
1497# Clickhouse FROM FINAL modifier
1498# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
1499class Final(Expression):
1500    pass
1501
1502
1503class Offset(Expression):
1504    arg_types = {"this": False, "expression": True}
1505
1506
1507class Order(Expression):
1508    arg_types = {"this": False, "expressions": True}
1509
1510
1511# hive specific sorts
1512# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
1513class Cluster(Order):
1514    pass
1515
1516
1517class Distribute(Order):
1518    pass
1519
1520
1521class Sort(Order):
1522    pass
1523
1524
1525class Ordered(Expression):
1526    arg_types = {"this": True, "desc": True, "nulls_first": True}
1527
1528
1529class Property(Expression):
1530    arg_types = {"this": True, "value": True}
1531
1532
1533class AfterJournalProperty(Property):
1534    arg_types = {"no": True, "dual": False, "local": False}
1535
1536
1537class AlgorithmProperty(Property):
1538    arg_types = {"this": True}
1539
1540
1541class AutoIncrementProperty(Property):
1542    arg_types = {"this": True}
1543
1544
1545class BlockCompressionProperty(Property):
1546    arg_types = {"autotemp": False, "always": False, "default": True, "manual": True, "never": True}
1547
1548
1549class CharacterSetProperty(Property):
1550    arg_types = {"this": True, "default": True}
1551
1552
1553class ChecksumProperty(Property):
1554    arg_types = {"on": False, "default": False}
1555
1556
1557class CollateProperty(Property):
1558    arg_types = {"this": True}
1559
1560
1561class DataBlocksizeProperty(Property):
1562    arg_types = {"size": False, "units": False, "min": False, "default": False}
1563
1564
1565class DefinerProperty(Property):
1566    arg_types = {"this": True}
1567
1568
1569class DistKeyProperty(Property):
1570    arg_types = {"this": True}
1571
1572
1573class DistStyleProperty(Property):
1574    arg_types = {"this": True}
1575
1576
1577class EngineProperty(Property):
1578    arg_types = {"this": True}
1579
1580
1581class ExecuteAsProperty(Property):
1582    arg_types = {"this": True}
1583
1584
1585class ExternalProperty(Property):
1586    arg_types = {"this": False}
1587
1588
1589class FallbackProperty(Property):
1590    arg_types = {"no": True, "protection": False}
1591
1592
1593class FileFormatProperty(Property):
1594    arg_types = {"this": True}
1595
1596
1597class FreespaceProperty(Property):
1598    arg_types = {"this": True, "percent": False}
1599
1600
1601class InputOutputFormat(Expression):
1602    arg_types = {"input_format": False, "output_format": False}
1603
1604
1605class IsolatedLoadingProperty(Property):
1606    arg_types = {
1607        "no": True,
1608        "concurrent": True,
1609        "for_all": True,
1610        "for_insert": True,
1611        "for_none": True,
1612    }
1613
1614
1615class JournalProperty(Property):
1616    arg_types = {"no": True, "dual": False, "before": False}
1617
1618
1619class LanguageProperty(Property):
1620    arg_types = {"this": True}
1621
1622
1623class LikeProperty(Property):
1624    arg_types = {"this": True, "expressions": False}
1625
1626
1627class LocationProperty(Property):
1628    arg_types = {"this": True}
1629
1630
1631class LockingProperty(Property):
1632    arg_types = {
1633        "this": False,
1634        "kind": True,
1635        "for_or_in": True,
1636        "lock_type": True,
1637        "override": False,
1638    }
1639
1640
1641class LogProperty(Property):
1642    arg_types = {"no": True}
1643
1644
1645class MaterializedProperty(Property):
1646    arg_types = {"this": False}
1647
1648
1649class MergeBlockRatioProperty(Property):
1650    arg_types = {"this": False, "no": False, "default": False, "percent": False}
1651
1652
1653class NoPrimaryIndexProperty(Property):
1654    arg_types = {"this": False}
1655
1656
1657class OnCommitProperty(Property):
1658    arg_type = {"this": False}
1659
1660
1661class PartitionedByProperty(Property):
1662    arg_types = {"this": True}
1663
1664
1665class ReturnsProperty(Property):
1666    arg_types = {"this": True, "is_table": False, "table": False}
1667
1668
1669class RowFormatProperty(Property):
1670    arg_types = {"this": True}
1671
1672
1673class RowFormatDelimitedProperty(Property):
1674    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
1675    arg_types = {
1676        "fields": False,
1677        "escaped": False,
1678        "collection_items": False,
1679        "map_keys": False,
1680        "lines": False,
1681        "null": False,
1682        "serde": False,
1683    }
1684
1685
1686class RowFormatSerdeProperty(Property):
1687    arg_types = {"this": True}
1688
1689
1690class SchemaCommentProperty(Property):
1691    arg_types = {"this": True}
1692
1693
1694class SerdeProperties(Property):
1695    arg_types = {"expressions": True}
1696
1697
1698class SetProperty(Property):
1699    arg_types = {"multi": True}
1700
1701
1702class SortKeyProperty(Property):
1703    arg_types = {"this": True, "compound": False}
1704
1705
1706class SqlSecurityProperty(Property):
1707    arg_types = {"definer": True}
1708
1709
1710class StabilityProperty(Property):
1711    arg_types = {"this": True}
1712
1713
1714class TableFormatProperty(Property):
1715    arg_types = {"this": True}
1716
1717
1718class TemporaryProperty(Property):
1719    arg_types = {"global_": True}
1720
1721
1722class TransientProperty(Property):
1723    arg_types = {"this": False}
1724
1725
1726class VolatileProperty(Property):
1727    arg_types = {"this": False}
1728
1729
1730class WithDataProperty(Property):
1731    arg_types = {"no": True, "statistics": False}
1732
1733
1734class WithJournalTableProperty(Property):
1735    arg_types = {"this": True}
1736
1737
1738class Properties(Expression):
1739    arg_types = {"expressions": True}
1740
1741    NAME_TO_PROPERTY = {
1742        "ALGORITHM": AlgorithmProperty,
1743        "AUTO_INCREMENT": AutoIncrementProperty,
1744        "CHARACTER SET": CharacterSetProperty,
1745        "COLLATE": CollateProperty,
1746        "COMMENT": SchemaCommentProperty,
1747        "DEFINER": DefinerProperty,
1748        "DISTKEY": DistKeyProperty,
1749        "DISTSTYLE": DistStyleProperty,
1750        "ENGINE": EngineProperty,
1751        "EXECUTE AS": ExecuteAsProperty,
1752        "FORMAT": FileFormatProperty,
1753        "LANGUAGE": LanguageProperty,
1754        "LOCATION": LocationProperty,
1755        "PARTITIONED_BY": PartitionedByProperty,
1756        "RETURNS": ReturnsProperty,
1757        "ROW_FORMAT": RowFormatProperty,
1758        "SORTKEY": SortKeyProperty,
1759        "TABLE_FORMAT": TableFormatProperty,
1760    }
1761
1762    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
1763
1764    # CREATE property locations
1765    # Form: schema specified
1766    #   create [POST_CREATE]
1767    #     table a [POST_NAME]
1768    #     (b int) [POST_SCHEMA]
1769    #     with ([POST_WITH])
1770    #     index (b) [POST_INDEX]
1771    #
1772    # Form: alias selection
1773    #   create [POST_CREATE]
1774    #     table a [POST_NAME]
1775    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
1776    #     index (c) [POST_INDEX]
1777    class Location(AutoName):
1778        POST_CREATE = auto()
1779        POST_NAME = auto()
1780        POST_SCHEMA = auto()
1781        POST_WITH = auto()
1782        POST_ALIAS = auto()
1783        POST_EXPRESSION = auto()
1784        POST_INDEX = auto()
1785        UNSUPPORTED = auto()
1786
1787    @classmethod
1788    def from_dict(cls, properties_dict) -> Properties:
1789        expressions = []
1790        for key, value in properties_dict.items():
1791            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
1792            if property_cls:
1793                expressions.append(property_cls(this=convert(value)))
1794            else:
1795                expressions.append(Property(this=Literal.string(key), value=convert(value)))
1796
1797        return cls(expressions=expressions)
1798
1799
1800class Qualify(Expression):
1801    pass
1802
1803
1804# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
1805class Return(Expression):
1806    pass
1807
1808
1809class Reference(Expression):
1810    arg_types = {"this": True, "expressions": False, "options": False}
1811
1812
1813class Tuple(Expression):
1814    arg_types = {"expressions": False}
1815
1816
1817class Subqueryable(Unionable):
1818    def subquery(self, alias=None, copy=True) -> Subquery:
1819        """
1820        Convert this expression to an aliased expression that can be used as a Subquery.
1821
1822        Example:
1823            >>> subquery = Select().select("x").from_("tbl").subquery()
1824            >>> Select().select("x").from_(subquery).sql()
1825            'SELECT x FROM (SELECT x FROM tbl)'
1826
1827        Args:
1828            alias (str | Identifier): an optional alias for the subquery
1829            copy (bool): if `False`, modify this expression instance in-place.
1830
1831        Returns:
1832            Alias: the subquery
1833        """
1834        instance = _maybe_copy(self, copy)
1835        return Subquery(
1836            this=instance,
1837            alias=TableAlias(this=to_identifier(alias)) if alias else None,
1838        )
1839
1840    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
1841        raise NotImplementedError
1842
1843    @property
1844    def ctes(self):
1845        with_ = self.args.get("with")
1846        if not with_:
1847            return []
1848        return with_.expressions
1849
1850    @property
1851    def selects(self):
1852        raise NotImplementedError("Subqueryable objects must implement `selects`")
1853
1854    @property
1855    def named_selects(self):
1856        raise NotImplementedError("Subqueryable objects must implement `named_selects`")
1857
1858    def with_(
1859        self,
1860        alias,
1861        as_,
1862        recursive=None,
1863        append=True,
1864        dialect=None,
1865        copy=True,
1866        **opts,
1867    ):
1868        """
1869        Append to or set the common table expressions.
1870
1871        Example:
1872            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
1873            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
1874
1875        Args:
1876            alias (str | Expression): the SQL code string to parse as the table name.
1877                If an `Expression` instance is passed, this is used as-is.
1878            as_ (str | Expression): the SQL code string to parse as the table expression.
1879                If an `Expression` instance is passed, it will be used as-is.
1880            recursive (bool): set the RECURSIVE part of the expression. Defaults to `False`.
1881            append (bool): if `True`, add to any existing expressions.
1882                Otherwise, this resets the expressions.
1883            dialect (str): the dialect used to parse the input expression.
1884            copy (bool): if `False`, modify this expression instance in-place.
1885            opts (kwargs): other options to use to parse the input expressions.
1886
1887        Returns:
1888            Select: the modified expression.
1889        """
1890        alias_expression = maybe_parse(
1891            alias,
1892            dialect=dialect,
1893            into=TableAlias,
1894            **opts,
1895        )
1896        as_expression = maybe_parse(
1897            as_,
1898            dialect=dialect,
1899            **opts,
1900        )
1901        cte = CTE(
1902            this=as_expression,
1903            alias=alias_expression,
1904        )
1905        return _apply_child_list_builder(
1906            cte,
1907            instance=self,
1908            arg="with",
1909            append=append,
1910            copy=copy,
1911            into=With,
1912            properties={"recursive": recursive or False},
1913        )
1914
1915
1916QUERY_MODIFIERS = {
1917    "match": False,
1918    "laterals": False,
1919    "joins": False,
1920    "pivots": False,
1921    "where": False,
1922    "group": False,
1923    "having": False,
1924    "qualify": False,
1925    "windows": False,
1926    "distribute": False,
1927    "sort": False,
1928    "cluster": False,
1929    "order": False,
1930    "limit": False,
1931    "offset": False,
1932    "lock": False,
1933    "sample": False,
1934}
1935
1936
1937class Table(Expression):
1938    arg_types = {
1939        "this": True,
1940        "alias": False,
1941        "db": False,
1942        "catalog": False,
1943        "laterals": False,
1944        "joins": False,
1945        "pivots": False,
1946        "hints": False,
1947        "system_time": False,
1948    }
1949
1950    @property
1951    def db(self) -> str:
1952        return self.text("db")
1953
1954    @property
1955    def catalog(self) -> str:
1956        return self.text("catalog")
1957
1958
1959# See the TSQL "Querying data in a system-versioned temporal table" page
1960class SystemTime(Expression):
1961    arg_types = {
1962        "this": False,
1963        "expression": False,
1964        "kind": True,
1965    }
1966
1967
1968class Union(Subqueryable):
1969    arg_types = {
1970        "with": False,
1971        "this": True,
1972        "expression": True,
1973        "distinct": False,
1974        **QUERY_MODIFIERS,
1975    }
1976
1977    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
1978        """
1979        Set the LIMIT expression.
1980
1981        Example:
1982            >>> select("1").union(select("1")).limit(1).sql()
1983            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
1984
1985        Args:
1986            expression (str | int | Expression): the SQL code string to parse.
1987                This can also be an integer.
1988                If a `Limit` instance is passed, this is used as-is.
1989                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
1990            dialect (str): the dialect used to parse the input expression.
1991            copy (bool): if `False`, modify this expression instance in-place.
1992            opts (kwargs): other options to use to parse the input expressions.
1993
1994        Returns:
1995            Select: The limited subqueryable.
1996        """
1997        return (
1998            select("*")
1999            .from_(self.subquery(alias="_l_0", copy=copy))
2000            .limit(expression, dialect=dialect, copy=False, **opts)
2001        )
2002
2003    def select(
2004        self,
2005        *expressions: ExpOrStr,
2006        append: bool = True,
2007        dialect: DialectType = None,
2008        copy: bool = True,
2009        **opts,
2010    ) -> Union:
2011        """Append to or set the SELECT of the union recursively.
2012
2013        Example:
2014            >>> from sqlglot import parse_one
2015            >>> parse_one("select a from x union select a from y union select a from z").select("b").sql()
2016            'SELECT a, b FROM x UNION SELECT a, b FROM y UNION SELECT a, b FROM z'
2017
2018        Args:
2019            *expressions: the SQL code strings to parse.
2020                If an `Expression` instance is passed, it will be used as-is.
2021            append: if `True`, add to any existing expressions.
2022                Otherwise, this resets the expressions.
2023            dialect: the dialect used to parse the input expressions.
2024            copy: if `False`, modify this expression instance in-place.
2025            opts: other options to use to parse the input expressions.
2026
2027        Returns:
2028            Union: the modified expression.
2029        """
2030        this = self.copy() if copy else self
2031        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
2032        this.expression.unnest().select(
2033            *expressions, append=append, dialect=dialect, copy=False, **opts
2034        )
2035        return this
2036
2037    @property
2038    def named_selects(self):
2039        return self.this.unnest().named_selects
2040
2041    @property
2042    def is_star(self) -> bool:
2043        return self.this.is_star or self.expression.is_star
2044
2045    @property
2046    def selects(self):
2047        return self.this.unnest().selects
2048
2049    @property
2050    def left(self):
2051        return self.this
2052
2053    @property
2054    def right(self):
2055        return self.expression
2056
2057
2058class Except(Union):
2059    pass
2060
2061
2062class Intersect(Union):
2063    pass
2064
2065
2066class Unnest(UDTF):
2067    arg_types = {
2068        "expressions": True,
2069        "ordinality": False,
2070        "alias": False,
2071        "offset": False,
2072    }
2073
2074
2075class Update(Expression):
2076    arg_types = {
2077        "with": False,
2078        "this": False,
2079        "expressions": True,
2080        "from": False,
2081        "where": False,
2082        "returning": False,
2083    }
2084
2085
2086class Values(UDTF):
2087    arg_types = {
2088        "expressions": True,
2089        "ordinality": False,
2090        "alias": False,
2091    }
2092
2093
2094class Var(Expression):
2095    pass
2096
2097
2098class Schema(Expression):
2099    arg_types = {"this": False, "expressions": False}
2100
2101
2102# Used to represent the FOR UPDATE and FOR SHARE locking read types.
2103# https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html
2104class Lock(Expression):
2105    arg_types = {"update": True}
2106
2107
2108class Select(Subqueryable):
2109    arg_types = {
2110        "with": False,
2111        "kind": False,
2112        "expressions": False,
2113        "hint": False,
2114        "distinct": False,
2115        "into": False,
2116        "from": False,
2117        **QUERY_MODIFIERS,
2118    }
2119
2120    def from_(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2121        """
2122        Set the FROM expression.
2123
2124        Example:
2125            >>> Select().from_("tbl").select("x").sql()
2126            'SELECT x FROM tbl'
2127
2128        Args:
2129            *expressions (str | Expression): the SQL code strings to parse.
2130                If a `From` instance is passed, this is used as-is.
2131                If another `Expression` instance is passed, it will be wrapped in a `From`.
2132            append (bool): if `True`, add to any existing expressions.
2133                Otherwise, this flattens all the `From` expression into a single expression.
2134            dialect (str): the dialect used to parse the input expression.
2135            copy (bool): if `False`, modify this expression instance in-place.
2136            opts (kwargs): other options to use to parse the input expressions.
2137
2138        Returns:
2139            Select: the modified expression.
2140        """
2141        return _apply_child_list_builder(
2142            *expressions,
2143            instance=self,
2144            arg="from",
2145            append=append,
2146            copy=copy,
2147            prefix="FROM",
2148            into=From,
2149            dialect=dialect,
2150            **opts,
2151        )
2152
2153    def group_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2154        """
2155        Set the GROUP BY expression.
2156
2157        Example:
2158            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
2159            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
2160
2161        Args:
2162            *expressions (str | Expression): the SQL code strings to parse.
2163                If a `Group` instance is passed, this is used as-is.
2164                If another `Expression` instance is passed, it will be wrapped in a `Group`.
2165                If nothing is passed in then a group by is not applied to the expression
2166            append (bool): if `True`, add to any existing expressions.
2167                Otherwise, this flattens all the `Group` expression into a single expression.
2168            dialect (str): the dialect used to parse the input expression.
2169            copy (bool): if `False`, modify this expression instance in-place.
2170            opts (kwargs): other options to use to parse the input expressions.
2171
2172        Returns:
2173            Select: the modified expression.
2174        """
2175        if not expressions:
2176            return self if not copy else self.copy()
2177        return _apply_child_list_builder(
2178            *expressions,
2179            instance=self,
2180            arg="group",
2181            append=append,
2182            copy=copy,
2183            prefix="GROUP BY",
2184            into=Group,
2185            dialect=dialect,
2186            **opts,
2187        )
2188
2189    def order_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2190        """
2191        Set the ORDER BY expression.
2192
2193        Example:
2194            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
2195            'SELECT x FROM tbl ORDER BY x DESC'
2196
2197        Args:
2198            *expressions (str | Expression): the SQL code strings to parse.
2199                If a `Group` instance is passed, this is used as-is.
2200                If another `Expression` instance is passed, it will be wrapped in a `Order`.
2201            append (bool): if `True`, add to any existing expressions.
2202                Otherwise, this flattens all the `Order` expression into a single expression.
2203            dialect (str): the dialect used to parse the input expression.
2204            copy (bool): if `False`, modify this expression instance in-place.
2205            opts (kwargs): other options to use to parse the input expressions.
2206
2207        Returns:
2208            Select: the modified expression.
2209        """
2210        return _apply_child_list_builder(
2211            *expressions,
2212            instance=self,
2213            arg="order",
2214            append=append,
2215            copy=copy,
2216            prefix="ORDER BY",
2217            into=Order,
2218            dialect=dialect,
2219            **opts,
2220        )
2221
2222    def sort_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2223        """
2224        Set the SORT BY expression.
2225
2226        Example:
2227            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql()
2228            'SELECT x FROM tbl SORT BY x DESC'
2229
2230        Args:
2231            *expressions (str | Expression): the SQL code strings to parse.
2232                If a `Group` instance is passed, this is used as-is.
2233                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
2234            append (bool): if `True`, add to any existing expressions.
2235                Otherwise, this flattens all the `Order` expression into a single expression.
2236            dialect (str): the dialect used to parse the input expression.
2237            copy (bool): if `False`, modify this expression instance in-place.
2238            opts (kwargs): other options to use to parse the input expressions.
2239
2240        Returns:
2241            Select: the modified expression.
2242        """
2243        return _apply_child_list_builder(
2244            *expressions,
2245            instance=self,
2246            arg="sort",
2247            append=append,
2248            copy=copy,
2249            prefix="SORT BY",
2250            into=Sort,
2251            dialect=dialect,
2252            **opts,
2253        )
2254
2255    def cluster_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2256        """
2257        Set the CLUSTER BY expression.
2258
2259        Example:
2260            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql()
2261            'SELECT x FROM tbl CLUSTER BY x DESC'
2262
2263        Args:
2264            *expressions (str | Expression): the SQL code strings to parse.
2265                If a `Group` instance is passed, this is used as-is.
2266                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
2267            append (bool): if `True`, add to any existing expressions.
2268                Otherwise, this flattens all the `Order` expression into a single expression.
2269            dialect (str): the dialect used to parse the input expression.
2270            copy (bool): if `False`, modify this expression instance in-place.
2271            opts (kwargs): other options to use to parse the input expressions.
2272
2273        Returns:
2274            Select: the modified expression.
2275        """
2276        return _apply_child_list_builder(
2277            *expressions,
2278            instance=self,
2279            arg="cluster",
2280            append=append,
2281            copy=copy,
2282            prefix="CLUSTER BY",
2283            into=Cluster,
2284            dialect=dialect,
2285            **opts,
2286        )
2287
2288    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
2289        """
2290        Set the LIMIT expression.
2291
2292        Example:
2293            >>> Select().from_("tbl").select("x").limit(10).sql()
2294            'SELECT x FROM tbl LIMIT 10'
2295
2296        Args:
2297            expression (str | int | Expression): the SQL code string to parse.
2298                This can also be an integer.
2299                If a `Limit` instance is passed, this is used as-is.
2300                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2301            dialect (str): the dialect used to parse the input expression.
2302            copy (bool): if `False`, modify this expression instance in-place.
2303            opts (kwargs): other options to use to parse the input expressions.
2304
2305        Returns:
2306            Select: the modified expression.
2307        """
2308        return _apply_builder(
2309            expression=expression,
2310            instance=self,
2311            arg="limit",
2312            into=Limit,
2313            prefix="LIMIT",
2314            dialect=dialect,
2315            copy=copy,
2316            **opts,
2317        )
2318
2319    def offset(self, expression, dialect=None, copy=True, **opts) -> Select:
2320        """
2321        Set the OFFSET expression.
2322
2323        Example:
2324            >>> Select().from_("tbl").select("x").offset(10).sql()
2325            'SELECT x FROM tbl OFFSET 10'
2326
2327        Args:
2328            expression (str | int | Expression): the SQL code string to parse.
2329                This can also be an integer.
2330                If a `Offset` instance is passed, this is used as-is.
2331                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
2332            dialect (str): the dialect used to parse the input expression.
2333            copy (bool): if `False`, modify this expression instance in-place.
2334            opts (kwargs): other options to use to parse the input expressions.
2335
2336        Returns:
2337            Select: the modified expression.
2338        """
2339        return _apply_builder(
2340            expression=expression,
2341            instance=self,
2342            arg="offset",
2343            into=Offset,
2344            prefix="OFFSET",
2345            dialect=dialect,
2346            copy=copy,
2347            **opts,
2348        )
2349
2350    def select(
2351        self,
2352        *expressions: ExpOrStr,
2353        append: bool = True,
2354        dialect: DialectType = None,
2355        copy: bool = True,
2356        **opts,
2357    ) -> Select:
2358        """
2359        Append to or set the SELECT expressions.
2360
2361        Example:
2362            >>> Select().select("x", "y").sql()
2363            'SELECT x, y'
2364
2365        Args:
2366            *expressions: the SQL code strings to parse.
2367                If an `Expression` instance is passed, it will be used as-is.
2368            append: if `True`, add to any existing expressions.
2369                Otherwise, this resets the expressions.
2370            dialect: the dialect used to parse the input expressions.
2371            copy: if `False`, modify this expression instance in-place.
2372            opts: other options to use to parse the input expressions.
2373
2374        Returns:
2375            Select: the modified expression.
2376        """
2377        return _apply_list_builder(
2378            *expressions,
2379            instance=self,
2380            arg="expressions",
2381            append=append,
2382            dialect=dialect,
2383            copy=copy,
2384            **opts,
2385        )
2386
2387    def lateral(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2388        """
2389        Append to or set the LATERAL expressions.
2390
2391        Example:
2392            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
2393            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
2394
2395        Args:
2396            *expressions (str | Expression): the SQL code strings to parse.
2397                If an `Expression` instance is passed, it will be used as-is.
2398            append (bool): if `True`, add to any existing expressions.
2399                Otherwise, this resets the expressions.
2400            dialect (str): the dialect used to parse the input expressions.
2401            copy (bool): if `False`, modify this expression instance in-place.
2402            opts (kwargs): other options to use to parse the input expressions.
2403
2404        Returns:
2405            Select: the modified expression.
2406        """
2407        return _apply_list_builder(
2408            *expressions,
2409            instance=self,
2410            arg="laterals",
2411            append=append,
2412            into=Lateral,
2413            prefix="LATERAL VIEW",
2414            dialect=dialect,
2415            copy=copy,
2416            **opts,
2417        )
2418
2419    def join(
2420        self,
2421        expression,
2422        on=None,
2423        using=None,
2424        append=True,
2425        join_type=None,
2426        join_alias=None,
2427        dialect=None,
2428        copy=True,
2429        **opts,
2430    ) -> Select:
2431        """
2432        Append to or set the JOIN expressions.
2433
2434        Example:
2435            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
2436            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
2437
2438            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
2439            'SELECT 1 FROM a JOIN b USING (x, y, z)'
2440
2441            Use `join_type` to change the type of join:
2442
2443            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
2444            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
2445
2446        Args:
2447            expression (str | Expression): the SQL code string to parse.
2448                If an `Expression` instance is passed, it will be used as-is.
2449            on (str | Expression): optionally specify the join "on" criteria as a SQL string.
2450                If an `Expression` instance is passed, it will be used as-is.
2451            using (str | Expression): optionally specify the join "using" criteria as a SQL string.
2452                If an `Expression` instance is passed, it will be used as-is.
2453            append (bool): if `True`, add to any existing expressions.
2454                Otherwise, this resets the expressions.
2455            join_type (str): If set, alter the parsed join type
2456            dialect (str): the dialect used to parse the input expressions.
2457            copy (bool): if `False`, modify this expression instance in-place.
2458            opts (kwargs): other options to use to parse the input expressions.
2459
2460        Returns:
2461            Select: the modified expression.
2462        """
2463        parse_args = {"dialect": dialect, **opts}
2464
2465        try:
2466            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
2467        except ParseError:
2468            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
2469
2470        join = expression if isinstance(expression, Join) else Join(this=expression)
2471
2472        if isinstance(join.this, Select):
2473            join.this.replace(join.this.subquery())
2474
2475        if join_type:
2476            natural: t.Optional[Token]
2477            side: t.Optional[Token]
2478            kind: t.Optional[Token]
2479
2480            natural, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
2481
2482            if natural:
2483                join.set("natural", True)
2484            if side:
2485                join.set("side", side.text)
2486            if kind:
2487                join.set("kind", kind.text)
2488
2489        if on:
2490            on = and_(*ensure_collection(on), dialect=dialect, **opts)
2491            join.set("on", on)
2492
2493        if using:
2494            join = _apply_list_builder(
2495                *ensure_collection(using),
2496                instance=join,
2497                arg="using",
2498                append=append,
2499                copy=copy,
2500                **opts,
2501            )
2502
2503        if join_alias:
2504            join.set("this", alias_(join.this, join_alias, table=True))
2505        return _apply_list_builder(
2506            join,
2507            instance=self,
2508            arg="joins",
2509            append=append,
2510            copy=copy,
2511            **opts,
2512        )
2513
2514    def where(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2515        """
2516        Append to or set the WHERE expressions.
2517
2518        Example:
2519            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
2520            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
2521
2522        Args:
2523            *expressions (str | Expression): the SQL code strings to parse.
2524                If an `Expression` instance is passed, it will be used as-is.
2525                Multiple expressions are combined with an AND operator.
2526            append (bool): if `True`, AND the new expressions to any existing expression.
2527                Otherwise, this resets the expression.
2528            dialect (str): the dialect used to parse the input expressions.
2529            copy (bool): if `False`, modify this expression instance in-place.
2530            opts (kwargs): other options to use to parse the input expressions.
2531
2532        Returns:
2533            Select: the modified expression.
2534        """
2535        return _apply_conjunction_builder(
2536            *expressions,
2537            instance=self,
2538            arg="where",
2539            append=append,
2540            into=Where,
2541            dialect=dialect,
2542            copy=copy,
2543            **opts,
2544        )
2545
2546    def having(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2547        """
2548        Append to or set the HAVING expressions.
2549
2550        Example:
2551            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
2552            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
2553
2554        Args:
2555            *expressions (str | Expression): the SQL code strings to parse.
2556                If an `Expression` instance is passed, it will be used as-is.
2557                Multiple expressions are combined with an AND operator.
2558            append (bool): if `True`, AND the new expressions to any existing expression.
2559                Otherwise, this resets the expression.
2560            dialect (str): the dialect used to parse the input expressions.
2561            copy (bool): if `False`, modify this expression instance in-place.
2562            opts (kwargs): other options to use to parse the input expressions.
2563
2564        Returns:
2565            Select: the modified expression.
2566        """
2567        return _apply_conjunction_builder(
2568            *expressions,
2569            instance=self,
2570            arg="having",
2571            append=append,
2572            into=Having,
2573            dialect=dialect,
2574            copy=copy,
2575            **opts,
2576        )
2577
2578    def window(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2579        return _apply_list_builder(
2580            *expressions,
2581            instance=self,
2582            arg="windows",
2583            append=append,
2584            into=Window,
2585            dialect=dialect,
2586            copy=copy,
2587            **opts,
2588        )
2589
2590    def qualify(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2591        return _apply_conjunction_builder(
2592            *expressions,
2593            instance=self,
2594            arg="qualify",
2595            append=append,
2596            into=Qualify,
2597            dialect=dialect,
2598            copy=copy,
2599            **opts,
2600        )
2601
2602    def distinct(self, distinct=True, copy=True) -> Select:
2603        """
2604        Set the OFFSET expression.
2605
2606        Example:
2607            >>> Select().from_("tbl").select("x").distinct().sql()
2608            'SELECT DISTINCT x FROM tbl'
2609
2610        Args:
2611            distinct (bool): whether the Select should be distinct
2612            copy (bool): if `False`, modify this expression instance in-place.
2613
2614        Returns:
2615            Select: the modified expression.
2616        """
2617        instance = _maybe_copy(self, copy)
2618        instance.set("distinct", Distinct() if distinct else None)
2619        return instance
2620
2621    def ctas(self, table, properties=None, dialect=None, copy=True, **opts) -> Create:
2622        """
2623        Convert this expression to a CREATE TABLE AS statement.
2624
2625        Example:
2626            >>> Select().select("*").from_("tbl").ctas("x").sql()
2627            'CREATE TABLE x AS SELECT * FROM tbl'
2628
2629        Args:
2630            table (str | Expression): the SQL code string to parse as the table name.
2631                If another `Expression` instance is passed, it will be used as-is.
2632            properties (dict): an optional mapping of table properties
2633            dialect (str): the dialect used to parse the input table.
2634            copy (bool): if `False`, modify this expression instance in-place.
2635            opts (kwargs): other options to use to parse the input table.
2636
2637        Returns:
2638            Create: the CREATE TABLE AS expression
2639        """
2640        instance = _maybe_copy(self, copy)
2641        table_expression = maybe_parse(
2642            table,
2643            into=Table,
2644            dialect=dialect,
2645            **opts,
2646        )
2647        properties_expression = None
2648        if properties:
2649            properties_expression = Properties.from_dict(properties)
2650
2651        return Create(
2652            this=table_expression,
2653            kind="table",
2654            expression=instance,
2655            properties=properties_expression,
2656        )
2657
2658    def lock(self, update: bool = True, copy: bool = True) -> Select:
2659        """
2660        Set the locking read mode for this expression.
2661
2662        Examples:
2663            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
2664            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
2665
2666            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
2667            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
2668
2669        Args:
2670            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
2671            copy: if `False`, modify this expression instance in-place.
2672
2673        Returns:
2674            The modified expression.
2675        """
2676
2677        inst = _maybe_copy(self, copy)
2678        inst.set("lock", Lock(update=update))
2679
2680        return inst
2681
2682    @property
2683    def named_selects(self) -> t.List[str]:
2684        return [e.output_name for e in self.expressions if e.alias_or_name]
2685
2686    @property
2687    def is_star(self) -> bool:
2688        return any(expression.is_star for expression in self.expressions)
2689
2690    @property
2691    def selects(self) -> t.List[Expression]:
2692        return self.expressions
2693
2694
2695class Subquery(DerivedTable, Unionable):
2696    arg_types = {
2697        "this": True,
2698        "alias": False,
2699        "with": False,
2700        **QUERY_MODIFIERS,
2701    }
2702
2703    def unnest(self):
2704        """
2705        Returns the first non subquery.
2706        """
2707        expression = self
2708        while isinstance(expression, Subquery):
2709            expression = expression.this
2710        return expression
2711
2712    @property
2713    def is_star(self) -> bool:
2714        return self.this.is_star
2715
2716    @property
2717    def output_name(self):
2718        return self.alias
2719
2720
2721class TableSample(Expression):
2722    arg_types = {
2723        "this": False,
2724        "method": False,
2725        "bucket_numerator": False,
2726        "bucket_denominator": False,
2727        "bucket_field": False,
2728        "percent": False,
2729        "rows": False,
2730        "size": False,
2731        "seed": False,
2732        "kind": False,
2733    }
2734
2735
2736class Tag(Expression):
2737    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
2738
2739    arg_types = {
2740        "this": False,
2741        "prefix": False,
2742        "postfix": False,
2743    }
2744
2745
2746class Pivot(Expression):
2747    arg_types = {
2748        "this": False,
2749        "alias": False,
2750        "expressions": True,
2751        "field": True,
2752        "unpivot": True,
2753    }
2754
2755
2756class Window(Expression):
2757    arg_types = {
2758        "this": True,
2759        "partition_by": False,
2760        "order": False,
2761        "spec": False,
2762        "alias": False,
2763    }
2764
2765
2766class WindowSpec(Expression):
2767    arg_types = {
2768        "kind": False,
2769        "start": False,
2770        "start_side": False,
2771        "end": False,
2772        "end_side": False,
2773    }
2774
2775
2776class Where(Expression):
2777    pass
2778
2779
2780class Star(Expression):
2781    arg_types = {"except": False, "replace": False}
2782
2783    @property
2784    def name(self) -> str:
2785        return "*"
2786
2787    @property
2788    def output_name(self):
2789        return self.name
2790
2791
2792class Parameter(Expression):
2793    arg_types = {"this": True, "wrapped": False}
2794
2795
2796class SessionParameter(Expression):
2797    arg_types = {"this": True, "kind": False}
2798
2799
2800class Placeholder(Expression):
2801    arg_types = {"this": False}
2802
2803
2804class Null(Condition):
2805    arg_types: t.Dict[str, t.Any] = {}
2806
2807    @property
2808    def name(self) -> str:
2809        return "NULL"
2810
2811
2812class Boolean(Condition):
2813    pass
2814
2815
2816class DataType(Expression):
2817    arg_types = {
2818        "this": True,
2819        "expressions": False,
2820        "nested": False,
2821        "values": False,
2822        "prefix": False,
2823    }
2824
2825    class Type(AutoName):
2826        CHAR = auto()
2827        NCHAR = auto()
2828        VARCHAR = auto()
2829        NVARCHAR = auto()
2830        TEXT = auto()
2831        MEDIUMTEXT = auto()
2832        LONGTEXT = auto()
2833        MEDIUMBLOB = auto()
2834        LONGBLOB = auto()
2835        BINARY = auto()
2836        VARBINARY = auto()
2837        INT = auto()
2838        UINT = auto()
2839        TINYINT = auto()
2840        UTINYINT = auto()
2841        SMALLINT = auto()
2842        USMALLINT = auto()
2843        BIGINT = auto()
2844        UBIGINT = auto()
2845        FLOAT = auto()
2846        DOUBLE = auto()
2847        DECIMAL = auto()
2848        BIGDECIMAL = auto()
2849        BIT = auto()
2850        BOOLEAN = auto()
2851        JSON = auto()
2852        JSONB = auto()
2853        INTERVAL = auto()
2854        TIME = auto()
2855        TIMESTAMP = auto()
2856        TIMESTAMPTZ = auto()
2857        TIMESTAMPLTZ = auto()
2858        DATE = auto()
2859        DATETIME = auto()
2860        ARRAY = auto()
2861        MAP = auto()
2862        UUID = auto()
2863        GEOGRAPHY = auto()
2864        GEOMETRY = auto()
2865        STRUCT = auto()
2866        NULLABLE = auto()
2867        HLLSKETCH = auto()
2868        HSTORE = auto()
2869        SUPER = auto()
2870        SERIAL = auto()
2871        SMALLSERIAL = auto()
2872        BIGSERIAL = auto()
2873        XML = auto()
2874        UNIQUEIDENTIFIER = auto()
2875        MONEY = auto()
2876        SMALLMONEY = auto()
2877        ROWVERSION = auto()
2878        IMAGE = auto()
2879        VARIANT = auto()
2880        OBJECT = auto()
2881        INET = auto()
2882        NULL = auto()
2883        UNKNOWN = auto()  # Sentinel value, useful for type annotation
2884
2885    TEXT_TYPES = {
2886        Type.CHAR,
2887        Type.NCHAR,
2888        Type.VARCHAR,
2889        Type.NVARCHAR,
2890        Type.TEXT,
2891    }
2892
2893    INTEGER_TYPES = {
2894        Type.INT,
2895        Type.TINYINT,
2896        Type.SMALLINT,
2897        Type.BIGINT,
2898    }
2899
2900    FLOAT_TYPES = {
2901        Type.FLOAT,
2902        Type.DOUBLE,
2903    }
2904
2905    NUMERIC_TYPES = {*INTEGER_TYPES, *FLOAT_TYPES}
2906
2907    TEMPORAL_TYPES = {
2908        Type.TIMESTAMP,
2909        Type.TIMESTAMPTZ,
2910        Type.TIMESTAMPLTZ,
2911        Type.DATE,
2912        Type.DATETIME,
2913    }
2914
2915    @classmethod
2916    def build(
2917        cls, dtype: str | DataType | DataType.Type, dialect: DialectType = None, **kwargs
2918    ) -> DataType:
2919        from sqlglot import parse_one
2920
2921        if isinstance(dtype, str):
2922            if dtype.upper() in cls.Type.__members__:
2923                data_type_exp: t.Optional[Expression] = DataType(this=DataType.Type[dtype.upper()])
2924            else:
2925                data_type_exp = parse_one(dtype, read=dialect, into=DataType)
2926            if data_type_exp is None:
2927                raise ValueError(f"Unparsable data type value: {dtype}")
2928        elif isinstance(dtype, DataType.Type):
2929            data_type_exp = DataType(this=dtype)
2930        elif isinstance(dtype, DataType):
2931            return dtype
2932        else:
2933            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
2934        return DataType(**{**data_type_exp.args, **kwargs})
2935
2936    def is_type(self, dtype: DataType.Type) -> bool:
2937        return self.this == dtype
2938
2939
2940# https://www.postgresql.org/docs/15/datatype-pseudo.html
2941class PseudoType(Expression):
2942    pass
2943
2944
2945class StructKwarg(Expression):
2946    arg_types = {"this": True, "expression": True}
2947
2948
2949# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
2950class SubqueryPredicate(Predicate):
2951    pass
2952
2953
2954class All(SubqueryPredicate):
2955    pass
2956
2957
2958class Any(SubqueryPredicate):
2959    pass
2960
2961
2962class Exists(SubqueryPredicate):
2963    pass
2964
2965
2966# Commands to interact with the databases or engines. For most of the command
2967# expressions we parse whatever comes after the command's name as a string.
2968class Command(Expression):
2969    arg_types = {"this": True, "expression": False}
2970
2971
2972class Transaction(Expression):
2973    arg_types = {"this": False, "modes": False}
2974
2975
2976class Commit(Expression):
2977    arg_types = {"chain": False}
2978
2979
2980class Rollback(Expression):
2981    arg_types = {"savepoint": False}
2982
2983
2984class AlterTable(Expression):
2985    arg_types = {"this": True, "actions": True, "exists": False}
2986
2987
2988class AddConstraint(Expression):
2989    arg_types = {"this": False, "expression": False, "enforced": False}
2990
2991
2992class DropPartition(Expression):
2993    arg_types = {"expressions": True, "exists": False}
2994
2995
2996# Binary expressions like (ADD a b)
2997class Binary(Expression):
2998    arg_types = {"this": True, "expression": True}
2999
3000    @property
3001    def left(self):
3002        return self.this
3003
3004    @property
3005    def right(self):
3006        return self.expression
3007
3008
3009class Add(Binary):
3010    pass
3011
3012
3013class Connector(Binary, Condition):
3014    pass
3015
3016
3017class And(Connector):
3018    pass
3019
3020
3021class Or(Connector):
3022    pass
3023
3024
3025class BitwiseAnd(Binary):
3026    pass
3027
3028
3029class BitwiseLeftShift(Binary):
3030    pass
3031
3032
3033class BitwiseOr(Binary):
3034    pass
3035
3036
3037class BitwiseRightShift(Binary):
3038    pass
3039
3040
3041class BitwiseXor(Binary):
3042    pass
3043
3044
3045class Div(Binary):
3046    pass
3047
3048
3049class Overlaps(Binary):
3050    pass
3051
3052
3053class Dot(Binary):
3054    @property
3055    def name(self) -> str:
3056        return self.expression.name
3057
3058    @classmethod
3059    def build(self, expressions: t.Sequence[Expression]) -> Dot:
3060        """Build a Dot object with a sequence of expressions."""
3061        if len(expressions) < 2:
3062            raise ValueError(f"Dot requires >= 2 expressions.")
3063
3064        a, b, *expressions = expressions
3065        dot = Dot(this=a, expression=b)
3066
3067        for expression in expressions:
3068            dot = Dot(this=dot, expression=expression)
3069
3070        return dot
3071
3072
3073class DPipe(Binary):
3074    pass
3075
3076
3077class EQ(Binary, Predicate):
3078    pass
3079
3080
3081class NullSafeEQ(Binary, Predicate):
3082    pass
3083
3084
3085class NullSafeNEQ(Binary, Predicate):
3086    pass
3087
3088
3089class Distance(Binary):
3090    pass
3091
3092
3093class Escape(Binary):
3094    pass
3095
3096
3097class Glob(Binary, Predicate):
3098    pass
3099
3100
3101class GT(Binary, Predicate):
3102    pass
3103
3104
3105class GTE(Binary, Predicate):
3106    pass
3107
3108
3109class ILike(Binary, Predicate):
3110    pass
3111
3112
3113class ILikeAny(Binary, Predicate):
3114    pass
3115
3116
3117class IntDiv(Binary):
3118    pass
3119
3120
3121class Is(Binary, Predicate):
3122    pass
3123
3124
3125class Kwarg(Binary):
3126    """Kwarg in special functions like func(kwarg => y)."""
3127
3128
3129class Like(Binary, Predicate):
3130    pass
3131
3132
3133class LikeAny(Binary, Predicate):
3134    pass
3135
3136
3137class LT(Binary, Predicate):
3138    pass
3139
3140
3141class LTE(Binary, Predicate):
3142    pass
3143
3144
3145class Mod(Binary):
3146    pass
3147
3148
3149class Mul(Binary):
3150    pass
3151
3152
3153class NEQ(Binary, Predicate):
3154    pass
3155
3156
3157class SimilarTo(Binary, Predicate):
3158    pass
3159
3160
3161class Slice(Binary):
3162    arg_types = {"this": False, "expression": False}
3163
3164
3165class Sub(Binary):
3166    pass
3167
3168
3169class ArrayOverlaps(Binary):
3170    pass
3171
3172
3173# Unary Expressions
3174# (NOT a)
3175class Unary(Expression):
3176    pass
3177
3178
3179class BitwiseNot(Unary):
3180    pass
3181
3182
3183class Not(Unary, Condition):
3184    pass
3185
3186
3187class Paren(Unary, Condition):
3188    arg_types = {"this": True, "with": False}
3189
3190
3191class Neg(Unary):
3192    pass
3193
3194
3195class Alias(Expression):
3196    arg_types = {"this": True, "alias": False}
3197
3198    @property
3199    def output_name(self):
3200        return self.alias
3201
3202
3203class Aliases(Expression):
3204    arg_types = {"this": True, "expressions": True}
3205
3206    @property
3207    def aliases(self):
3208        return self.expressions
3209
3210
3211class AtTimeZone(Expression):
3212    arg_types = {"this": True, "zone": True}
3213
3214
3215class Between(Predicate):
3216    arg_types = {"this": True, "low": True, "high": True}
3217
3218
3219class Bracket(Condition):
3220    arg_types = {"this": True, "expressions": True}
3221
3222
3223class Distinct(Expression):
3224    arg_types = {"expressions": False, "on": False}
3225
3226
3227class In(Predicate):
3228    arg_types = {
3229        "this": True,
3230        "expressions": False,
3231        "query": False,
3232        "unnest": False,
3233        "field": False,
3234        "is_global": False,
3235    }
3236
3237
3238class TimeUnit(Expression):
3239    """Automatically converts unit arg into a var."""
3240
3241    arg_types = {"unit": False}
3242
3243    def __init__(self, **args):
3244        unit = args.get("unit")
3245        if isinstance(unit, (Column, Literal)):
3246            args["unit"] = Var(this=unit.name)
3247        elif isinstance(unit, Week):
3248            unit.set("this", Var(this=unit.this.name))
3249        super().__init__(**args)
3250
3251
3252class Interval(TimeUnit):
3253    arg_types = {"this": False, "unit": False}
3254
3255
3256class IgnoreNulls(Expression):
3257    pass
3258
3259
3260class RespectNulls(Expression):
3261    pass
3262
3263
3264# Functions
3265class Func(Condition):
3266    """
3267    The base class for all function expressions.
3268
3269    Attributes:
3270        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
3271            treated as a variable length argument and the argument's value will be stored as a list.
3272        _sql_names (list): determines the SQL name (1st item in the list) and aliases (subsequent items)
3273            for this function expression. These values are used to map this node to a name during parsing
3274            as well as to provide the function's name during SQL string generation. By default the SQL
3275            name is set to the expression's class name transformed to snake case.
3276    """
3277
3278    is_var_len_args = False
3279
3280    @classmethod
3281    def from_arg_list(cls, args):
3282        if cls.is_var_len_args:
3283            all_arg_keys = list(cls.arg_types)
3284            # If this function supports variable length argument treat the last argument as such.
3285            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
3286            num_non_var = len(non_var_len_arg_keys)
3287
3288            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
3289            args_dict[all_arg_keys[-1]] = args[num_non_var:]
3290        else:
3291            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
3292
3293        return cls(**args_dict)
3294
3295    @classmethod
3296    def sql_names(cls):
3297        if cls is Func:
3298            raise NotImplementedError(
3299                "SQL name is only supported by concrete function implementations"
3300            )
3301        if "_sql_names" not in cls.__dict__:
3302            cls._sql_names = [camel_to_snake_case(cls.__name__)]
3303        return cls._sql_names
3304
3305    @classmethod
3306    def sql_name(cls):
3307        return cls.sql_names()[0]
3308
3309    @classmethod
3310    def default_parser_mappings(cls):
3311        return {name: cls.from_arg_list for name in cls.sql_names()}
3312
3313
3314class AggFunc(Func):
3315    pass
3316
3317
3318class Abs(Func):
3319    pass
3320
3321
3322class Anonymous(Func):
3323    arg_types = {"this": True, "expressions": False}
3324    is_var_len_args = True
3325
3326
3327# https://docs.snowflake.com/en/sql-reference/functions/hll
3328# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
3329class Hll(AggFunc):
3330    arg_types = {"this": True, "expressions": False}
3331    is_var_len_args = True
3332
3333
3334class ApproxDistinct(AggFunc):
3335    arg_types = {"this": True, "accuracy": False}
3336
3337
3338class Array(Func):
3339    arg_types = {"expressions": False}
3340    is_var_len_args = True
3341
3342
3343# https://docs.snowflake.com/en/sql-reference/functions/to_char
3344class ToChar(Func):
3345    arg_types = {"this": True, "format": False}
3346
3347
3348class GenerateSeries(Func):
3349    arg_types = {"start": True, "end": True, "step": False}
3350
3351
3352class ArrayAgg(AggFunc):
3353    pass
3354
3355
3356class ArrayAll(Func):
3357    arg_types = {"this": True, "expression": True}
3358
3359
3360class ArrayAny(Func):
3361    arg_types = {"this": True, "expression": True}
3362
3363
3364class ArrayConcat(Func):
3365    arg_types = {"this": True, "expressions": False}
3366    is_var_len_args = True
3367
3368
3369class ArrayContains(Binary, Func):
3370    pass
3371
3372
3373class ArrayContained(Binary):
3374    pass
3375
3376
3377class ArrayFilter(Func):
3378    arg_types = {"this": True, "expression": True}
3379    _sql_names = ["FILTER", "ARRAY_FILTER"]
3380
3381
3382class ArrayJoin(Func):
3383    arg_types = {"this": True, "expression": True, "null": False}
3384
3385
3386class ArraySize(Func):
3387    arg_types = {"this": True, "expression": False}
3388
3389
3390class ArraySort(Func):
3391    arg_types = {"this": True, "expression": False}
3392
3393
3394class ArraySum(Func):
3395    pass
3396
3397
3398class ArrayUnionAgg(AggFunc):
3399    pass
3400
3401
3402class Avg(AggFunc):
3403    pass
3404
3405
3406class AnyValue(AggFunc):
3407    pass
3408
3409
3410class Case(Func):
3411    arg_types = {"this": False, "ifs": True, "default": False}
3412
3413
3414class Cast(Func):
3415    arg_types = {"this": True, "to": True}
3416
3417    @property
3418    def name(self) -> str:
3419        return self.this.name
3420
3421    @property
3422    def to(self):
3423        return self.args["to"]
3424
3425    @property
3426    def output_name(self):
3427        return self.name
3428
3429    def is_type(self, dtype: DataType.Type) -> bool:
3430        return self.to.is_type(dtype)
3431
3432
3433class Collate(Binary):
3434    pass
3435
3436
3437class TryCast(Cast):
3438    pass
3439
3440
3441class Ceil(Func):
3442    arg_types = {"this": True, "decimals": False}
3443    _sql_names = ["CEIL", "CEILING"]
3444
3445
3446class Coalesce(Func):
3447    arg_types = {"this": True, "expressions": False}
3448    is_var_len_args = True
3449
3450
3451class Concat(Func):
3452    arg_types = {"expressions": True}
3453    is_var_len_args = True
3454
3455
3456class ConcatWs(Concat):
3457    _sql_names = ["CONCAT_WS"]
3458
3459
3460class Count(AggFunc):
3461    arg_types = {"this": False}
3462
3463
3464class CountIf(AggFunc):
3465    pass
3466
3467
3468class CurrentDate(Func):
3469    arg_types = {"this": False}
3470
3471
3472class CurrentDatetime(Func):
3473    arg_types = {"this": False}
3474
3475
3476class CurrentTime(Func):
3477    arg_types = {"this": False}
3478
3479
3480class CurrentTimestamp(Func):
3481    arg_types = {"this": False}
3482
3483
3484class CurrentUser(Func):
3485    arg_types = {"this": False}
3486
3487
3488class DateAdd(Func, TimeUnit):
3489    arg_types = {"this": True, "expression": True, "unit": False}
3490
3491
3492class DateSub(Func, TimeUnit):
3493    arg_types = {"this": True, "expression": True, "unit": False}
3494
3495
3496class DateDiff(Func, TimeUnit):
3497    _sql_names = ["DATEDIFF", "DATE_DIFF"]
3498    arg_types = {"this": True, "expression": True, "unit": False}
3499
3500
3501class DateTrunc(Func):
3502    arg_types = {"unit": True, "this": True, "zone": False}
3503
3504
3505class DatetimeAdd(Func, TimeUnit):
3506    arg_types = {"this": True, "expression": True, "unit": False}
3507
3508
3509class DatetimeSub(Func, TimeUnit):
3510    arg_types = {"this": True, "expression": True, "unit": False}
3511
3512
3513class DatetimeDiff(Func, TimeUnit):
3514    arg_types = {"this": True, "expression": True, "unit": False}
3515
3516
3517class DatetimeTrunc(Func, TimeUnit):
3518    arg_types = {"this": True, "unit": True, "zone": False}
3519
3520
3521class DayOfWeek(Func):
3522    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
3523
3524
3525class DayOfMonth(Func):
3526    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
3527
3528
3529class DayOfYear(Func):
3530    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
3531
3532
3533class WeekOfYear(Func):
3534    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
3535
3536
3537class LastDateOfMonth(Func):
3538    pass
3539
3540
3541class Extract(Func):
3542    arg_types = {"this": True, "expression": True}
3543
3544
3545class TimestampAdd(Func, TimeUnit):
3546    arg_types = {"this": True, "expression": True, "unit": False}
3547
3548
3549class TimestampSub(Func, TimeUnit):
3550    arg_types = {"this": True, "expression": True, "unit": False}
3551
3552
3553class TimestampDiff(Func, TimeUnit):
3554    arg_types = {"this": True, "expression": True, "unit": False}
3555
3556
3557class TimestampTrunc(Func, TimeUnit):
3558    arg_types = {"this": True, "unit": True, "zone": False}
3559
3560
3561class TimeAdd(Func, TimeUnit):
3562    arg_types = {"this": True, "expression": True, "unit": False}
3563
3564
3565class TimeSub(Func, TimeUnit):
3566    arg_types = {"this": True, "expression": True, "unit": False}
3567
3568
3569class TimeDiff(Func, TimeUnit):
3570    arg_types = {"this": True, "expression": True, "unit": False}
3571
3572
3573class TimeTrunc(Func, TimeUnit):
3574    arg_types = {"this": True, "unit": True, "zone": False}
3575
3576
3577class DateFromParts(Func):
3578    _sql_names = ["DATEFROMPARTS"]
3579    arg_types = {"year": True, "month": True, "day": True}
3580
3581
3582class DateStrToDate(Func):
3583    pass
3584
3585
3586class DateToDateStr(Func):
3587    pass
3588
3589
3590class DateToDi(Func):
3591    pass
3592
3593
3594class Day(Func):
3595    pass
3596
3597
3598class Decode(Func):
3599    arg_types = {"this": True, "charset": True, "replace": False}
3600
3601
3602class DiToDate(Func):
3603    pass
3604
3605
3606class Encode(Func):
3607    arg_types = {"this": True, "charset": True}
3608
3609
3610class Exp(Func):
3611    pass
3612
3613
3614class Explode(Func):
3615    pass
3616
3617
3618class ExponentialTimeDecayedAvg(AggFunc):
3619    arg_types = {"this": True, "time": False, "decay": False}
3620
3621
3622class Floor(Func):
3623    arg_types = {"this": True, "decimals": False}
3624
3625
3626class Greatest(Func):
3627    arg_types = {"this": True, "expressions": False}
3628    is_var_len_args = True
3629
3630
3631class GroupConcat(Func):
3632    arg_types = {"this": True, "separator": False}
3633
3634
3635class GroupUniqArray(AggFunc):
3636    arg_types = {"this": True, "size": False}
3637
3638
3639class Hex(Func):
3640    pass
3641
3642
3643class Histogram(AggFunc):
3644    arg_types = {"this": True, "bins": False}
3645
3646
3647class If(Func):
3648    arg_types = {"this": True, "true": True, "false": False}
3649
3650
3651class IfNull(Func):
3652    arg_types = {"this": True, "expression": False}
3653    _sql_names = ["IFNULL", "NVL"]
3654
3655
3656class Initcap(Func):
3657    pass
3658
3659
3660class JSONKeyValue(Expression):
3661    arg_types = {"this": True, "expression": True}
3662
3663
3664class JSONObject(Func):
3665    arg_types = {
3666        "expressions": False,
3667        "null_handling": False,
3668        "unique_keys": False,
3669        "return_type": False,
3670        "format_json": False,
3671        "encoding": False,
3672    }
3673
3674
3675class JSONBContains(Binary):
3676    _sql_names = ["JSONB_CONTAINS"]
3677
3678
3679class JSONExtract(Binary, Func):
3680    _sql_names = ["JSON_EXTRACT"]
3681
3682
3683class JSONExtractScalar(JSONExtract):
3684    _sql_names = ["JSON_EXTRACT_SCALAR"]
3685
3686
3687class JSONBExtract(JSONExtract):
3688    _sql_names = ["JSONB_EXTRACT"]
3689
3690
3691class JSONBExtractScalar(JSONExtract):
3692    _sql_names = ["JSONB_EXTRACT_SCALAR"]
3693
3694
3695class JSONFormat(Func):
3696    arg_types = {"this": False, "options": False}
3697    _sql_names = ["JSON_FORMAT"]
3698
3699
3700class Least(Func):
3701    arg_types = {"expressions": False}
3702    is_var_len_args = True
3703
3704
3705class Length(Func):
3706    pass
3707
3708
3709class Levenshtein(Func):
3710    arg_types = {
3711        "this": True,
3712        "expression": False,
3713        "ins_cost": False,
3714        "del_cost": False,
3715        "sub_cost": False,
3716    }
3717
3718
3719class Ln(Func):
3720    pass
3721
3722
3723class Log(Func):
3724    arg_types = {"this": True, "expression": False}
3725
3726
3727class Log2(Func):
3728    pass
3729
3730
3731class Log10(Func):
3732    pass
3733
3734
3735class LogicalOr(AggFunc):
3736    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
3737
3738
3739class LogicalAnd(AggFunc):
3740    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
3741
3742
3743class Lower(Func):
3744    _sql_names = ["LOWER", "LCASE"]
3745
3746
3747class Map(Func):
3748    arg_types = {"keys": False, "values": False}
3749
3750
3751class VarMap(Func):
3752    arg_types = {"keys": True, "values": True}
3753    is_var_len_args = True
3754
3755
3756# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
3757class MatchAgainst(Func):
3758    arg_types = {"this": True, "expressions": True, "modifier": False}
3759
3760
3761class Max(AggFunc):
3762    arg_types = {"this": True, "expressions": False}
3763    is_var_len_args = True
3764
3765
3766class Min(AggFunc):
3767    arg_types = {"this": True, "expressions": False}
3768    is_var_len_args = True
3769
3770
3771class Month(Func):
3772    pass
3773
3774
3775class Nvl2(Func):
3776    arg_types = {"this": True, "true": True, "false": False}
3777
3778
3779class Posexplode(Func):
3780    pass
3781
3782
3783class Pow(Binary, Func):
3784    _sql_names = ["POWER", "POW"]
3785
3786
3787class PercentileCont(AggFunc):
3788    pass
3789
3790
3791class PercentileDisc(AggFunc):
3792    pass
3793
3794
3795class Quantile(AggFunc):
3796    arg_types = {"this": True, "quantile": True}
3797
3798
3799# Clickhouse-specific:
3800# https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/quantiles/#quantiles
3801class Quantiles(AggFunc):
3802    arg_types = {"parameters": True, "expressions": True}
3803    is_var_len_args = True
3804
3805
3806class QuantileIf(AggFunc):
3807    arg_types = {"parameters": True, "expressions": True}
3808
3809
3810class ApproxQuantile(Quantile):
3811    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
3812
3813
3814class RangeN(Func):
3815    arg_types = {"this": True, "expressions": True, "each": False}
3816
3817
3818class ReadCSV(Func):
3819    _sql_names = ["READ_CSV"]
3820    is_var_len_args = True
3821    arg_types = {"this": True, "expressions": False}
3822
3823
3824class Reduce(Func):
3825    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
3826
3827
3828class RegexpExtract(Func):
3829    arg_types = {
3830        "this": True,
3831        "expression": True,
3832        "position": False,
3833        "occurrence": False,
3834        "group": False,
3835    }
3836
3837
3838class RegexpLike(Func):
3839    arg_types = {"this": True, "expression": True, "flag": False}
3840
3841
3842class RegexpILike(Func):
3843    arg_types = {"this": True, "expression": True, "flag": False}
3844
3845
3846# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
3847# limit is the number of times a pattern is applied
3848class RegexpSplit(Func):
3849    arg_types = {"this": True, "expression": True, "limit": False}
3850
3851
3852class Repeat(Func):
3853    arg_types = {"this": True, "times": True}
3854
3855
3856class Round(Func):
3857    arg_types = {"this": True, "decimals": False}
3858
3859
3860class RowNumber(Func):
3861    arg_types: t.Dict[str, t.Any] = {}
3862
3863
3864class SafeDivide(Func):
3865    arg_types = {"this": True, "expression": True}
3866
3867
3868class SetAgg(AggFunc):
3869    pass
3870
3871
3872class SortArray(Func):
3873    arg_types = {"this": True, "asc": False}
3874
3875
3876class Split(Func):
3877    arg_types = {"this": True, "expression": True, "limit": False}
3878
3879
3880# Start may be omitted in the case of postgres
3881# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
3882class Substring(Func):
3883    arg_types = {"this": True, "start": False, "length": False}
3884
3885
3886class StrPosition(Func):
3887    arg_types = {
3888        "this": True,
3889        "substr": True,
3890        "position": False,
3891        "instance": False,
3892    }
3893
3894
3895class StrToDate(Func):
3896    arg_types = {"this": True, "format": True}
3897
3898
3899class StrToTime(Func):
3900    arg_types = {"this": True, "format": True}
3901
3902
3903# Spark allows unix_timestamp()
3904# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
3905class StrToUnix(Func):
3906    arg_types = {"this": False, "format": False}
3907
3908
3909class NumberToStr(Func):
3910    arg_types = {"this": True, "format": True}
3911
3912
3913class Struct(Func):
3914    arg_types = {"expressions": True}
3915    is_var_len_args = True
3916
3917
3918class StructExtract(Func):
3919    arg_types = {"this": True, "expression": True}
3920
3921
3922class Sum(AggFunc):
3923    pass
3924
3925
3926class Sqrt(Func):
3927    pass
3928
3929
3930class Stddev(AggFunc):
3931    pass
3932
3933
3934class StddevPop(AggFunc):
3935    pass
3936
3937
3938class StddevSamp(AggFunc):
3939    pass
3940
3941
3942class TimeToStr(Func):
3943    arg_types = {"this": True, "format": True}
3944
3945
3946class TimeToTimeStr(Func):
3947    pass
3948
3949
3950class TimeToUnix(Func):
3951    pass
3952
3953
3954class TimeStrToDate(Func):
3955    pass
3956
3957
3958class TimeStrToTime(Func):
3959    pass
3960
3961
3962class TimeStrToUnix(Func):
3963    pass
3964
3965
3966class Trim(Func):
3967    arg_types = {
3968        "this": True,
3969        "expression": False,
3970        "position": False,
3971        "collation": False,
3972    }
3973
3974
3975class TsOrDsAdd(Func, TimeUnit):
3976    arg_types = {"this": True, "expression": True, "unit": False}
3977
3978
3979class TsOrDsToDateStr(Func):
3980    pass
3981
3982
3983class TsOrDsToDate(Func):
3984    arg_types = {"this": True, "format": False}
3985
3986
3987class TsOrDiToDi(Func):
3988    pass
3989
3990
3991class Unhex(Func):
3992    pass
3993
3994
3995class UnixToStr(Func):
3996    arg_types = {"this": True, "format": False}
3997
3998
3999# https://prestodb.io/docs/current/functions/datetime.html
4000# presto has weird zone/hours/minutes
4001class UnixToTime(Func):
4002    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
4003
4004    SECONDS = Literal.string("seconds")
4005    MILLIS = Literal.string("millis")
4006    MICROS = Literal.string("micros")
4007
4008
4009class UnixToTimeStr(Func):
4010    pass
4011
4012
4013class Upper(Func):
4014    _sql_names = ["UPPER", "UCASE"]
4015
4016
4017class Variance(AggFunc):
4018    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
4019
4020
4021class VariancePop(AggFunc):
4022    _sql_names = ["VARIANCE_POP", "VAR_POP"]
4023
4024
4025class Week(Func):
4026    arg_types = {"this": True, "mode": False}
4027
4028
4029class XMLTable(Func):
4030    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
4031
4032
4033class Year(Func):
4034    pass
4035
4036
4037class Use(Expression):
4038    arg_types = {"this": True, "kind": False}
4039
4040
4041class Merge(Expression):
4042    arg_types = {"this": True, "using": True, "on": True, "expressions": True}
4043
4044
4045class When(Func):
4046    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
4047
4048
4049def _norm_arg(arg):
4050    return arg.lower() if type(arg) is str else arg
4051
4052
4053ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
4054
4055
4056# Helpers
4057@t.overload
4058def maybe_parse(
4059    sql_or_expression: ExpOrStr,
4060    *,
4061    into: t.Type[E],
4062    dialect: DialectType = None,
4063    prefix: t.Optional[str] = None,
4064    copy: bool = False,
4065    **opts,
4066) -> E:
4067    ...
4068
4069
4070@t.overload
4071def maybe_parse(
4072    sql_or_expression: str | E,
4073    *,
4074    into: t.Optional[IntoType] = None,
4075    dialect: DialectType = None,
4076    prefix: t.Optional[str] = None,
4077    copy: bool = False,
4078    **opts,
4079) -> E:
4080    ...
4081
4082
4083def maybe_parse(
4084    sql_or_expression: ExpOrStr,
4085    *,
4086    into: t.Optional[IntoType] = None,
4087    dialect: DialectType = None,
4088    prefix: t.Optional[str] = None,
4089    copy: bool = False,
4090    **opts,
4091) -> Expression:
4092    """Gracefully handle a possible string or expression.
4093
4094    Example:
4095        >>> maybe_parse("1")
4096        (LITERAL this: 1, is_string: False)
4097        >>> maybe_parse(to_identifier("x"))
4098        (IDENTIFIER this: x, quoted: False)
4099
4100    Args:
4101        sql_or_expression: the SQL code string or an expression
4102        into: the SQLGlot Expression to parse into
4103        dialect: the dialect used to parse the input expressions (in the case that an
4104            input expression is a SQL string).
4105        prefix: a string to prefix the sql with before it gets parsed
4106            (automatically includes a space)
4107        copy: whether or not to copy the expression.
4108        **opts: other options to use to parse the input expressions (again, in the case
4109            that an input expression is a SQL string).
4110
4111    Returns:
4112        Expression: the parsed or given expression.
4113    """
4114    if isinstance(sql_or_expression, Expression):
4115        if copy:
4116            return sql_or_expression.copy()
4117        return sql_or_expression
4118
4119    import sqlglot
4120
4121    sql = str(sql_or_expression)
4122    if prefix:
4123        sql = f"{prefix} {sql}"
4124    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
4125
4126
4127def _maybe_copy(instance, copy=True):
4128    return instance.copy() if copy else instance
4129
4130
4131def _is_wrong_expression(expression, into):
4132    return isinstance(expression, Expression) and not isinstance(expression, into)
4133
4134
4135def _apply_builder(
4136    expression,
4137    instance,
4138    arg,
4139    copy=True,
4140    prefix=None,
4141    into=None,
4142    dialect=None,
4143    **opts,
4144):
4145    if _is_wrong_expression(expression, into):
4146        expression = into(this=expression)
4147    instance = _maybe_copy(instance, copy)
4148    expression = maybe_parse(
4149        sql_or_expression=expression,
4150        prefix=prefix,
4151        into=into,
4152        dialect=dialect,
4153        **opts,
4154    )
4155    instance.set(arg, expression)
4156    return instance
4157
4158
4159def _apply_child_list_builder(
4160    *expressions,
4161    instance,
4162    arg,
4163    append=True,
4164    copy=True,
4165    prefix=None,
4166    into=None,
4167    dialect=None,
4168    properties=None,
4169    **opts,
4170):
4171    instance = _maybe_copy(instance, copy)
4172    parsed = []
4173    for expression in expressions:
4174        if _is_wrong_expression(expression, into):
4175            expression = into(expressions=[expression])
4176        expression = maybe_parse(
4177            expression,
4178            into=into,
4179            dialect=dialect,
4180            prefix=prefix,
4181            **opts,
4182        )
4183        parsed.extend(expression.expressions)
4184
4185    existing = instance.args.get(arg)
4186    if append and existing:
4187        parsed = existing.expressions + parsed
4188
4189    child = into(expressions=parsed)
4190    for k, v in (properties or {}).items():
4191        child.set(k, v)
4192    instance.set(arg, child)
4193    return instance
4194
4195
4196def _apply_list_builder(
4197    *expressions,
4198    instance,
4199    arg,
4200    append=True,
4201    copy=True,
4202    prefix=None,
4203    into=None,
4204    dialect=None,
4205    **opts,
4206):
4207    inst = _maybe_copy(instance, copy)
4208
4209    expressions = [
4210        maybe_parse(
4211            sql_or_expression=expression,
4212            into=into,
4213            prefix=prefix,
4214            dialect=dialect,
4215            **opts,
4216        )
4217        for expression in expressions
4218    ]
4219
4220    existing_expressions = inst.args.get(arg)
4221    if append and existing_expressions:
4222        expressions = existing_expressions + expressions
4223
4224    inst.set(arg, expressions)
4225    return inst
4226
4227
4228def _apply_conjunction_builder(
4229    *expressions,
4230    instance,
4231    arg,
4232    into=None,
4233    append=True,
4234    copy=True,
4235    dialect=None,
4236    **opts,
4237):
4238    expressions = [exp for exp in expressions if exp is not None and exp != ""]
4239    if not expressions:
4240        return instance
4241
4242    inst = _maybe_copy(instance, copy)
4243
4244    existing = inst.args.get(arg)
4245    if append and existing is not None:
4246        expressions = [existing.this if into else existing] + list(expressions)
4247
4248    node = and_(*expressions, dialect=dialect, **opts)
4249
4250    inst.set(arg, into(this=node) if into else node)
4251    return inst
4252
4253
4254def _combine(expressions, operator, dialect=None, **opts):
4255    expressions = [condition(expression, dialect=dialect, **opts) for expression in expressions]
4256    this = expressions[0]
4257    if expressions[1:]:
4258        this = _wrap_operator(this)
4259    for expression in expressions[1:]:
4260        this = operator(this=this, expression=_wrap_operator(expression))
4261    return this
4262
4263
4264def _wrap_operator(expression):
4265    if isinstance(expression, (And, Or, Not)):
4266        expression = Paren(this=expression)
4267    return expression
4268
4269
4270def union(left, right, distinct=True, dialect=None, **opts):
4271    """
4272    Initializes a syntax tree from one UNION expression.
4273
4274    Example:
4275        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
4276        'SELECT * FROM foo UNION SELECT * FROM bla'
4277
4278    Args:
4279        left (str | Expression): the SQL code string corresponding to the left-hand side.
4280            If an `Expression` instance is passed, it will be used as-is.
4281        right (str | Expression): the SQL code string corresponding to the right-hand side.
4282            If an `Expression` instance is passed, it will be used as-is.
4283        distinct (bool): set the DISTINCT flag if and only if this is true.
4284        dialect (str): the dialect used to parse the input expression.
4285        opts (kwargs): other options to use to parse the input expressions.
4286    Returns:
4287        Union: the syntax tree for the UNION expression.
4288    """
4289    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
4290    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
4291
4292    return Union(this=left, expression=right, distinct=distinct)
4293
4294
4295def intersect(left, right, distinct=True, dialect=None, **opts):
4296    """
4297    Initializes a syntax tree from one INTERSECT expression.
4298
4299    Example:
4300        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
4301        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
4302
4303    Args:
4304        left (str | Expression): the SQL code string corresponding to the left-hand side.
4305            If an `Expression` instance is passed, it will be used as-is.
4306        right (str | Expression): the SQL code string corresponding to the right-hand side.
4307            If an `Expression` instance is passed, it will be used as-is.
4308        distinct (bool): set the DISTINCT flag if and only if this is true.
4309        dialect (str): the dialect used to parse the input expression.
4310        opts (kwargs): other options to use to parse the input expressions.
4311    Returns:
4312        Intersect: the syntax tree for the INTERSECT expression.
4313    """
4314    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
4315    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
4316
4317    return Intersect(this=left, expression=right, distinct=distinct)
4318
4319
4320def except_(left, right, distinct=True, dialect=None, **opts):
4321    """
4322    Initializes a syntax tree from one EXCEPT expression.
4323
4324    Example:
4325        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
4326        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
4327
4328    Args:
4329        left (str | Expression): the SQL code string corresponding to the left-hand side.
4330            If an `Expression` instance is passed, it will be used as-is.
4331        right (str | Expression): the SQL code string corresponding to the right-hand side.
4332            If an `Expression` instance is passed, it will be used as-is.
4333        distinct (bool): set the DISTINCT flag if and only if this is true.
4334        dialect (str): the dialect used to parse the input expression.
4335        opts (kwargs): other options to use to parse the input expressions.
4336    Returns:
4337        Except: the syntax tree for the EXCEPT statement.
4338    """
4339    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
4340    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
4341
4342    return Except(this=left, expression=right, distinct=distinct)
4343
4344
4345def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
4346    """
4347    Initializes a syntax tree from one or multiple SELECT expressions.
4348
4349    Example:
4350        >>> select("col1", "col2").from_("tbl").sql()
4351        'SELECT col1, col2 FROM tbl'
4352
4353    Args:
4354        *expressions: the SQL code string to parse as the expressions of a
4355            SELECT statement. If an Expression instance is passed, this is used as-is.
4356        dialect: the dialect used to parse the input expressions (in the case that an
4357            input expression is a SQL string).
4358        **opts: other options to use to parse the input expressions (again, in the case
4359            that an input expression is a SQL string).
4360
4361    Returns:
4362        Select: the syntax tree for the SELECT statement.
4363    """
4364    return Select().select(*expressions, dialect=dialect, **opts)
4365
4366
4367def from_(*expressions, dialect=None, **opts) -> Select:
4368    """
4369    Initializes a syntax tree from a FROM expression.
4370
4371    Example:
4372        >>> from_("tbl").select("col1", "col2").sql()
4373        'SELECT col1, col2 FROM tbl'
4374
4375    Args:
4376        *expressions (str | Expression): the SQL code string to parse as the FROM expressions of a
4377            SELECT statement. If an Expression instance is passed, this is used as-is.
4378        dialect (str): the dialect used to parse the input expression (in the case that the
4379            input expression is a SQL string).
4380        **opts: other options to use to parse the input expressions (again, in the case
4381            that the input expression is a SQL string).
4382
4383    Returns:
4384        Select: the syntax tree for the SELECT statement.
4385    """
4386    return Select().from_(*expressions, dialect=dialect, **opts)
4387
4388
4389def update(
4390    table: str | Table,
4391    properties: dict,
4392    where: t.Optional[ExpOrStr] = None,
4393    from_: t.Optional[ExpOrStr] = None,
4394    dialect: DialectType = None,
4395    **opts,
4396) -> Update:
4397    """
4398    Creates an update statement.
4399
4400    Example:
4401        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
4402        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
4403
4404    Args:
4405        *properties: dictionary of properties to set which are
4406            auto converted to sql objects eg None -> NULL
4407        where: sql conditional parsed into a WHERE statement
4408        from_: sql statement parsed into a FROM statement
4409        dialect: the dialect used to parse the input expressions.
4410        **opts: other options to use to parse the input expressions.
4411
4412    Returns:
4413        Update: the syntax tree for the UPDATE statement.
4414    """
4415    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
4416    update_expr.set(
4417        "expressions",
4418        [
4419            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
4420            for k, v in properties.items()
4421        ],
4422    )
4423    if from_:
4424        update_expr.set(
4425            "from",
4426            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
4427        )
4428    if isinstance(where, Condition):
4429        where = Where(this=where)
4430    if where:
4431        update_expr.set(
4432            "where",
4433            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
4434        )
4435    return update_expr
4436
4437
4438def delete(
4439    table: ExpOrStr,
4440    where: t.Optional[ExpOrStr] = None,
4441    returning: t.Optional[ExpOrStr] = None,
4442    dialect: DialectType = None,
4443    **opts,
4444) -> Delete:
4445    """
4446    Builds a delete statement.
4447
4448    Example:
4449        >>> delete("my_table", where="id > 1").sql()
4450        'DELETE FROM my_table WHERE id > 1'
4451
4452    Args:
4453        where: sql conditional parsed into a WHERE statement
4454        returning: sql conditional parsed into a RETURNING statement
4455        dialect: the dialect used to parse the input expressions.
4456        **opts: other options to use to parse the input expressions.
4457
4458    Returns:
4459        Delete: the syntax tree for the DELETE statement.
4460    """
4461    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
4462    if where:
4463        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
4464    if returning:
4465        delete_expr = delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
4466    return delete_expr
4467
4468
4469def condition(expression, dialect=None, **opts) -> Condition:
4470    """
4471    Initialize a logical condition expression.
4472
4473    Example:
4474        >>> condition("x=1").sql()
4475        'x = 1'
4476
4477        This is helpful for composing larger logical syntax trees:
4478        >>> where = condition("x=1")
4479        >>> where = where.and_("y=1")
4480        >>> Select().from_("tbl").select("*").where(where).sql()
4481        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
4482
4483    Args:
4484        *expression (str | Expression): the SQL code string to parse.
4485            If an Expression instance is passed, this is used as-is.
4486        dialect (str): the dialect used to parse the input expression (in the case that the
4487            input expression is a SQL string).
4488        **opts: other options to use to parse the input expressions (again, in the case
4489            that the input expression is a SQL string).
4490
4491    Returns:
4492        Condition: the expression
4493    """
4494    return maybe_parse(  # type: ignore
4495        expression,
4496        into=Condition,
4497        dialect=dialect,
4498        **opts,
4499    )
4500
4501
4502def and_(*expressions, dialect=None, **opts) -> And:
4503    """
4504    Combine multiple conditions with an AND logical operator.
4505
4506    Example:
4507        >>> and_("x=1", and_("y=1", "z=1")).sql()
4508        'x = 1 AND (y = 1 AND z = 1)'
4509
4510    Args:
4511        *expressions (str | Expression): the SQL code strings to parse.
4512            If an Expression instance is passed, this is used as-is.
4513        dialect (str): the dialect used to parse the input expression.
4514        **opts: other options to use to parse the input expressions.
4515
4516    Returns:
4517        And: the new condition
4518    """
4519    return _combine(expressions, And, dialect, **opts)
4520
4521
4522def or_(*expressions, dialect=None, **opts) -> Or:
4523    """
4524    Combine multiple conditions with an OR logical operator.
4525
4526    Example:
4527        >>> or_("x=1", or_("y=1", "z=1")).sql()
4528        'x = 1 OR (y = 1 OR z = 1)'
4529
4530    Args:
4531        *expressions (str | Expression): the SQL code strings to parse.
4532            If an Expression instance is passed, this is used as-is.
4533        dialect (str): the dialect used to parse the input expression.
4534        **opts: other options to use to parse the input expressions.
4535
4536    Returns:
4537        Or: the new condition
4538    """
4539    return _combine(expressions, Or, dialect, **opts)
4540
4541
4542def not_(expression, dialect=None, **opts) -> Not:
4543    """
4544    Wrap a condition with a NOT operator.
4545
4546    Example:
4547        >>> not_("this_suit='black'").sql()
4548        "NOT this_suit = 'black'"
4549
4550    Args:
4551        expression (str | Expression): the SQL code strings to parse.
4552            If an Expression instance is passed, this is used as-is.
4553        dialect (str): the dialect used to parse the input expression.
4554        **opts: other options to use to parse the input expressions.
4555
4556    Returns:
4557        Not: the new condition
4558    """
4559    this = condition(
4560        expression,
4561        dialect=dialect,
4562        **opts,
4563    )
4564    return Not(this=_wrap_operator(this))
4565
4566
4567def paren(expression) -> Paren:
4568    return Paren(this=expression)
4569
4570
4571SAFE_IDENTIFIER_RE = re.compile(r"^[_a-zA-Z][\w]*$")
4572
4573
4574@t.overload
4575def to_identifier(name: None, quoted: t.Optional[bool] = None) -> None:
4576    ...
4577
4578
4579@t.overload
4580def to_identifier(name: str | Identifier, quoted: t.Optional[bool] = None) -> Identifier:
4581    ...
4582
4583
4584def to_identifier(name, quoted=None):
4585    """Builds an identifier.
4586
4587    Args:
4588        name: The name to turn into an identifier.
4589        quoted: Whether or not force quote the identifier.
4590
4591    Returns:
4592        The identifier ast node.
4593    """
4594
4595    if name is None:
4596        return None
4597
4598    if isinstance(name, Identifier):
4599        identifier = name
4600    elif isinstance(name, str):
4601        identifier = Identifier(
4602            this=name,
4603            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
4604        )
4605    else:
4606        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
4607    return identifier
4608
4609
4610INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
4611
4612
4613def to_interval(interval: str | Literal) -> Interval:
4614    """Builds an interval expression from a string like '1 day' or '5 months'."""
4615    if isinstance(interval, Literal):
4616        if not interval.is_string:
4617            raise ValueError("Invalid interval string.")
4618
4619        interval = interval.this
4620
4621    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
4622
4623    if not interval_parts:
4624        raise ValueError("Invalid interval string.")
4625
4626    return Interval(
4627        this=Literal.string(interval_parts.group(1)),
4628        unit=Var(this=interval_parts.group(2)),
4629    )
4630
4631
4632@t.overload
4633def to_table(sql_path: str | Table, **kwargs) -> Table:
4634    ...
4635
4636
4637@t.overload
4638def to_table(sql_path: None, **kwargs) -> None:
4639    ...
4640
4641
4642def to_table(sql_path: t.Optional[str | Table], **kwargs) -> t.Optional[Table]:
4643    """
4644    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
4645    If a table is passed in then that table is returned.
4646
4647    Args:
4648        sql_path: a `[catalog].[schema].[table]` string.
4649
4650    Returns:
4651        A table expression.
4652    """
4653    if sql_path is None or isinstance(sql_path, Table):
4654        return sql_path
4655    if not isinstance(sql_path, str):
4656        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
4657
4658    catalog, db, table_name = (to_identifier(x) for x in split_num_words(sql_path, ".", 3))
4659    return Table(this=table_name, db=db, catalog=catalog, **kwargs)
4660
4661
4662def to_column(sql_path: str | Column, **kwargs) -> Column:
4663    """
4664    Create a column from a `[table].[column]` sql path. Schema is optional.
4665
4666    If a column is passed in then that column is returned.
4667
4668    Args:
4669        sql_path: `[table].[column]` string
4670    Returns:
4671        Table: A column expression
4672    """
4673    if sql_path is None or isinstance(sql_path, Column):
4674        return sql_path
4675    if not isinstance(sql_path, str):
4676        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
4677    return column(*reversed(sql_path.split(".")), **kwargs)  # type: ignore
4678
4679
4680def alias_(
4681    expression: ExpOrStr,
4682    alias: str | Identifier,
4683    table: bool | t.Sequence[str | Identifier] = False,
4684    quoted: t.Optional[bool] = None,
4685    dialect: DialectType = None,
4686    **opts,
4687):
4688    """Create an Alias expression.
4689
4690    Example:
4691        >>> alias_('foo', 'bar').sql()
4692        'foo AS bar'
4693
4694        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
4695        '(SELECT 1, 2) AS bar(a, b)'
4696
4697    Args:
4698        expression: the SQL code strings to parse.
4699            If an Expression instance is passed, this is used as-is.
4700        alias: the alias name to use. If the name has
4701            special characters it is quoted.
4702        table: Whether or not to create a table alias, can also be a list of columns.
4703        quoted: whether or not to quote the alias
4704        dialect: the dialect used to parse the input expression.
4705        **opts: other options to use to parse the input expressions.
4706
4707    Returns:
4708        Alias: the aliased expression
4709    """
4710    exp = maybe_parse(expression, dialect=dialect, **opts)
4711    alias = to_identifier(alias, quoted=quoted)
4712
4713    if table:
4714        table_alias = TableAlias(this=alias)
4715        exp.set("alias", table_alias)
4716
4717        if not isinstance(table, bool):
4718            for column in table:
4719                table_alias.append("columns", to_identifier(column, quoted=quoted))
4720
4721        return exp
4722
4723    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
4724    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
4725    # for the complete Window expression.
4726    #
4727    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
4728
4729    if "alias" in exp.arg_types and not isinstance(exp, Window):
4730        exp = exp.copy()
4731        exp.set("alias", alias)
4732        return exp
4733    return Alias(this=exp, alias=alias)
4734
4735
4736def subquery(expression, alias=None, dialect=None, **opts):
4737    """
4738    Build a subquery expression.
4739
4740    Example:
4741        >>> subquery('select x from tbl', 'bar').select('x').sql()
4742        'SELECT x FROM (SELECT x FROM tbl) AS bar'
4743
4744    Args:
4745        expression (str | Expression): the SQL code strings to parse.
4746            If an Expression instance is passed, this is used as-is.
4747        alias (str | Expression): the alias name to use.
4748        dialect (str): the dialect used to parse the input expression.
4749        **opts: other options to use to parse the input expressions.
4750
4751    Returns:
4752        Select: a new select with the subquery expression included
4753    """
4754
4755    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
4756    return Select().from_(expression, dialect=dialect, **opts)
4757
4758
4759def column(
4760    col: str | Identifier,
4761    table: t.Optional[str | Identifier] = None,
4762    db: t.Optional[str | Identifier] = None,
4763    catalog: t.Optional[str | Identifier] = None,
4764    quoted: t.Optional[bool] = None,
4765) -> Column:
4766    """
4767    Build a Column.
4768
4769    Args:
4770        col: column name
4771        table: table name
4772        db: db name
4773        catalog: catalog name
4774        quoted: whether or not to force quote each part
4775    Returns:
4776        Column: column instance
4777    """
4778    return Column(
4779        this=to_identifier(col, quoted=quoted),
4780        table=to_identifier(table, quoted=quoted),
4781        db=to_identifier(db, quoted=quoted),
4782        catalog=to_identifier(catalog, quoted=quoted),
4783    )
4784
4785
4786def cast(expression: ExpOrStr, to: str | DataType | DataType.Type, **opts) -> Cast:
4787    """Cast an expression to a data type.
4788
4789    Example:
4790        >>> cast('x + 1', 'int').sql()
4791        'CAST(x + 1 AS INT)'
4792
4793    Args:
4794        expression: The expression to cast.
4795        to: The datatype to cast to.
4796
4797    Returns:
4798        A cast node.
4799    """
4800    expression = maybe_parse(expression, **opts)
4801    return Cast(this=expression, to=DataType.build(to, **opts))
4802
4803
4804def table_(table, db=None, catalog=None, quoted=None, alias=None) -> Table:
4805    """Build a Table.
4806
4807    Args:
4808        table (str | Expression): column name
4809        db (str | Expression): db name
4810        catalog (str | Expression): catalog name
4811
4812    Returns:
4813        Table: table instance
4814    """
4815    return Table(
4816        this=to_identifier(table, quoted=quoted),
4817        db=to_identifier(db, quoted=quoted),
4818        catalog=to_identifier(catalog, quoted=quoted),
4819        alias=TableAlias(this=to_identifier(alias)) if alias else None,
4820    )
4821
4822
4823def values(
4824    values: t.Iterable[t.Tuple[t.Any, ...]],
4825    alias: t.Optional[str] = None,
4826    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
4827) -> Values:
4828    """Build VALUES statement.
4829
4830    Example:
4831        >>> values([(1, '2')]).sql()
4832        "VALUES (1, '2')"
4833
4834    Args:
4835        values: values statements that will be converted to SQL
4836        alias: optional alias
4837        columns: Optional list of ordered column names or ordered dictionary of column names to types.
4838         If either are provided then an alias is also required.
4839         If a dictionary is provided then the first column of the values will be casted to the expected type
4840         in order to help with type inference.
4841
4842    Returns:
4843        Values: the Values expression object
4844    """
4845    if columns and not alias:
4846        raise ValueError("Alias is required when providing columns")
4847    table_alias = (
4848        TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
4849        if columns
4850        else TableAlias(this=to_identifier(alias) if alias else None)
4851    )
4852    expressions = [convert(tup) for tup in values]
4853    if columns and isinstance(columns, dict):
4854        types = list(columns.values())
4855        expressions[0].set(
4856            "expressions",
4857            [cast(x, types[i]) for i, x in enumerate(expressions[0].expressions)],
4858        )
4859    return Values(
4860        expressions=expressions,
4861        alias=table_alias,
4862    )
4863
4864
4865def var(name: t.Optional[ExpOrStr]) -> Var:
4866    """Build a SQL variable.
4867
4868    Example:
4869        >>> repr(var('x'))
4870        '(VAR this: x)'
4871
4872        >>> repr(var(column('x', table='y')))
4873        '(VAR this: x)'
4874
4875    Args:
4876        name: The name of the var or an expression who's name will become the var.
4877
4878    Returns:
4879        The new variable node.
4880    """
4881    if not name:
4882        raise ValueError("Cannot convert empty name into var.")
4883
4884    if isinstance(name, Expression):
4885        name = name.name
4886    return Var(this=name)
4887
4888
4889def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
4890    """Build ALTER TABLE... RENAME... expression
4891
4892    Args:
4893        old_name: The old name of the table
4894        new_name: The new name of the table
4895
4896    Returns:
4897        Alter table expression
4898    """
4899    old_table = to_table(old_name)
4900    new_table = to_table(new_name)
4901    return AlterTable(
4902        this=old_table,
4903        actions=[
4904            RenameTable(this=new_table),
4905        ],
4906    )
4907
4908
4909def convert(value) -> Expression:
4910    """Convert a python value into an expression object.
4911
4912    Raises an error if a conversion is not possible.
4913
4914    Args:
4915        value (Any): a python object
4916
4917    Returns:
4918        Expression: the equivalent expression object
4919    """
4920    if isinstance(value, Expression):
4921        return value
4922    if value is None:
4923        return NULL
4924    if isinstance(value, bool):
4925        return Boolean(this=value)
4926    if isinstance(value, str):
4927        return Literal.string(value)
4928    if isinstance(value, float) and math.isnan(value):
4929        return NULL
4930    if isinstance(value, numbers.Number):
4931        return Literal.number(value)
4932    if isinstance(value, tuple):
4933        return Tuple(expressions=[convert(v) for v in value])
4934    if isinstance(value, list):
4935        return Array(expressions=[convert(v) for v in value])
4936    if isinstance(value, dict):
4937        return Map(
4938            keys=[convert(k) for k in value],
4939            values=[convert(v) for v in value.values()],
4940        )
4941    if isinstance(value, datetime.datetime):
4942        datetime_literal = Literal.string(
4943            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
4944        )
4945        return TimeStrToTime(this=datetime_literal)
4946    if isinstance(value, datetime.date):
4947        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
4948        return DateStrToDate(this=date_literal)
4949    raise ValueError(f"Cannot convert {value}")
4950
4951
4952def replace_children(expression, fun, *args, **kwargs):
4953    """
4954    Replace children of an expression with the result of a lambda fun(child) -> exp.
4955    """
4956    for k, v in expression.args.items():
4957        is_list_arg = type(v) is list
4958
4959        child_nodes = v if is_list_arg else [v]
4960        new_child_nodes = []
4961
4962        for cn in child_nodes:
4963            if isinstance(cn, Expression):
4964                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
4965                    new_child_nodes.append(child_node)
4966                    child_node.parent = expression
4967                    child_node.arg_key = k
4968            else:
4969                new_child_nodes.append(cn)
4970
4971        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
4972
4973
4974def column_table_names(expression):
4975    """
4976    Return all table names referenced through columns in an expression.
4977
4978    Example:
4979        >>> import sqlglot
4980        >>> column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e"))
4981        ['c', 'a']
4982
4983    Args:
4984        expression (sqlglot.Expression): expression to find table names
4985
4986    Returns:
4987        list: A list of unique names
4988    """
4989    return list(dict.fromkeys(column.table for column in expression.find_all(Column)))
4990
4991
4992def table_name(table) -> str:
4993    """Get the full name of a table as a string.
4994
4995    Args:
4996        table (exp.Table | str): table expression node or string.
4997
4998    Examples:
4999        >>> from sqlglot import exp, parse_one
5000        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
5001        'a.b.c'
5002
5003    Returns:
5004        The table name.
5005    """
5006
5007    table = maybe_parse(table, into=Table)
5008
5009    if not table:
5010        raise ValueError(f"Cannot parse {table}")
5011
5012    return ".".join(
5013        part
5014        for part in (
5015            table.text("catalog"),
5016            table.text("db"),
5017            table.name,
5018        )
5019        if part
5020    )
5021
5022
5023def replace_tables(expression, mapping):
5024    """Replace all tables in expression according to the mapping.
5025
5026    Args:
5027        expression (sqlglot.Expression): expression node to be transformed and replaced.
5028        mapping (Dict[str, str]): mapping of table names.
5029
5030    Examples:
5031        >>> from sqlglot import exp, parse_one
5032        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
5033        'SELECT * FROM c'
5034
5035    Returns:
5036        The mapped expression.
5037    """
5038
5039    def _replace_tables(node):
5040        if isinstance(node, Table):
5041            new_name = mapping.get(table_name(node))
5042            if new_name:
5043                return to_table(
5044                    new_name,
5045                    **{k: v for k, v in node.args.items() if k not in ("this", "db", "catalog")},
5046                )
5047        return node
5048
5049    return expression.transform(_replace_tables)
5050
5051
5052def replace_placeholders(expression, *args, **kwargs):
5053    """Replace placeholders in an expression.
5054
5055    Args:
5056        expression (sqlglot.Expression): expression node to be transformed and replaced.
5057        args: positional names that will substitute unnamed placeholders in the given order.
5058        kwargs: keyword arguments that will substitute named placeholders.
5059
5060    Examples:
5061        >>> from sqlglot import exp, parse_one
5062        >>> replace_placeholders(
5063        ...     parse_one("select * from :tbl where ? = ?"),
5064        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
5065        ... ).sql()
5066        "SELECT * FROM foo WHERE str_col = 'b'"
5067
5068    Returns:
5069        The mapped expression.
5070    """
5071
5072    def _replace_placeholders(node, args, **kwargs):
5073        if isinstance(node, Placeholder):
5074            if node.name:
5075                new_name = kwargs.get(node.name)
5076                if new_name:
5077                    return convert(new_name)
5078            else:
5079                try:
5080                    return convert(next(args))
5081                except StopIteration:
5082                    pass
5083        return node
5084
5085    return expression.transform(_replace_placeholders, iter(args), **kwargs)
5086
5087
5088def expand(
5089    expression: Expression, sources: t.Dict[str, Subqueryable], copy: bool = True
5090) -> Expression:
5091    """Transforms an expression by expanding all referenced sources into subqueries.
5092
5093    Examples:
5094        >>> from sqlglot import parse_one
5095        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
5096        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
5097
5098        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
5099        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
5100
5101    Args:
5102        expression: The expression to expand.
5103        sources: A dictionary of name to Subqueryables.
5104        copy: Whether or not to copy the expression during transformation. Defaults to True.
5105
5106    Returns:
5107        The transformed expression.
5108    """
5109
5110    def _expand(node: Expression):
5111        if isinstance(node, Table):
5112            name = table_name(node)
5113            source = sources.get(name)
5114            if source:
5115                subquery = source.subquery(node.alias or name)
5116                subquery.comments = [f"source: {name}"]
5117                return subquery.transform(_expand, copy=False)
5118        return node
5119
5120    return expression.transform(_expand, copy=copy)
5121
5122
5123def func(name: str, *args, dialect: DialectType = None, **kwargs) -> Func:
5124    """
5125    Returns a Func expression.
5126
5127    Examples:
5128        >>> func("abs", 5).sql()
5129        'ABS(5)'
5130
5131        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
5132        'CAST(5 AS DOUBLE)'
5133
5134    Args:
5135        name: the name of the function to build.
5136        args: the args used to instantiate the function of interest.
5137        dialect: the source dialect.
5138        kwargs: the kwargs used to instantiate the function of interest.
5139
5140    Note:
5141        The arguments `args` and `kwargs` are mutually exclusive.
5142
5143    Returns:
5144        An instance of the function of interest, or an anonymous function, if `name` doesn't
5145        correspond to an existing `sqlglot.expressions.Func` class.
5146    """
5147    if args and kwargs:
5148        raise ValueError("Can't use both args and kwargs to instantiate a function.")
5149
5150    from sqlglot.dialects.dialect import Dialect
5151
5152    converted = [convert(arg) for arg in args]
5153    kwargs = {key: convert(value) for key, value in kwargs.items()}
5154
5155    parser = Dialect.get_or_raise(dialect)().parser()
5156    from_args_list = parser.FUNCTIONS.get(name.upper())
5157
5158    if from_args_list:
5159        function = from_args_list(converted) if converted else from_args_list.__self__(**kwargs)  # type: ignore
5160    else:
5161        kwargs = kwargs or {"expressions": converted}
5162        function = Anonymous(this=name, **kwargs)
5163
5164    for error_message in function.error_messages(converted):
5165        raise ValueError(error_message)
5166
5167    return function
5168
5169
5170def true():
5171    """
5172    Returns a true Boolean expression.
5173    """
5174    return Boolean(this=True)
5175
5176
5177def false():
5178    """
5179    Returns a false Boolean expression.
5180    """
5181    return Boolean(this=False)
5182
5183
5184def null():
5185    """
5186    Returns a Null expression.
5187    """
5188    return Null()
5189
5190
5191# TODO: deprecate this
5192TRUE = Boolean(this=True)
5193FALSE = Boolean(this=False)
5194NULL = Null()